Mid/Left/Right (1 Viewer)

[prasadgov]

I have a table with data as below

I need a query which would derive the fields from this data such as,
MessageType : IIF(Left([F1],4=":20C"),Right([F1],10)). I need o get the value as ICF0000325 (the second row)
But it is giving #Error

TIA

 

[GPGeorge]

Are you only interested in records which have :20C in the first four positions? What will you do with all of the other variations shown in the screenshot.

Where does the data come from? It is pretty ugly. Can you get a better version of the data from the source?

 

[Gasman]

Firstly your brackets are not correct for the Left function?
Secondly you need both True and False sections for the IIF function.

I dare say we are going to get more of these questions with this data.
I would create a function to strip out what you need from each type of record.
Some do not have the pattern :nn:: ? Are you sure you have imported the data correctly?

 

[prasadgov]

Are you only interested in records which have :20C in the first four positions? What will you do with all of the other variations shown in the screenshot.

Where does the data come from? It is pretty ugly. Can you get a better version of the data from the source?

I get the data from a text field import. There are derivations for others, such as for 36B it will 50000000 (EST Settle AMT) and 98A = 20240626 (EST Settle Date) and so on.
This is an hourly data. I read the data into another table and delete the original table data to receive next hour data.

 

[Gasman]

I am wondering if there is a way to store the relevant split needed in a table with the key identifiers?
That would be the most efficient I would expect, especially if new identifiers become a reality?

 

[GPGeorge]

I get the data from a text field import. There are derivations for others, such as for 36B it will 50000000 (EST Settle AMT) and 98A = 20240626 (EST Settle Date) and so on

I'm with the Gasman. You need a generalized function to parse the data in each record, rather than focusing on one type at a time.

All of the records in this small sample begin with a give character pattern:

:00X:

Where 0 means any digit and X means any letter.

Again, I agree with the suggestion of a Split() on the colon character, followed by a routine that inspects the elements of the resulting array for key values, i.e. the 3 character codes, and their corresponding residual values.

 

[Gasman]

All of the records in this small sample begin with a give character pattern:

Not all George
/Time/072323

 

[GPGeorge]

Not all George
/Time/072323

Hm. I suspect that might be a group delimiter, rather than a data record.

 

[ebs17]

SELECT Right(F1, 10) AS Result
FROM tbltcc01
WHERE F1 LIKE ":20C*"

 

[Gasman]

SELECT Right(F1, 10) AS Result
FROM tbltcc01
WHERE F1 LIKE ":20C*"

So multiple queries in a UNION?

 

[prasadgov]

SELECT Right(F1, 10) AS Result
FROM tbltcc01
WHERE F1 LIKE ":20C*"

This works for one condition. There are derivations for others, such as for 36B it will 50000000 (EST Settle AMT) and 98A = 20240626 (EST Settle Date) and so on. I am trying to use IIF or equivalent

 

[GPGeorge]

This works for one condition. There are derivations for others, such as for 36B it will 50000000 (EST Settle AMT) and 98A = 20240626 (EST Settle Date) and so on. I am trying to use IIF or equivalent

Do you know how to use the Split() Method? Would you consider trying it?

Can you provide a sample file of the kind of data involved?

 

[Gasman]

I think InstrRev() is more useful here?

 

[ebs17]

This works for one condition.

Of course. You only showed what should be isolated from one line.

There are derivations for others

Then you should also show a complete list with the original expression and the exact expression to be isolated from it. A complete list does not mean all (imaginable) 1000 lines, but all typical variants should be included, and in order for an investigation to work across all, the bad outliers from the norm must of course also be included.

This list should then ideally be such that it can be used directly as a table in an experiment; I don't have the capacity to copy it from an image.

 

[plog]

You provided 18 very different starting strings. Of those you gave us only 1 expected ending strings. That's not enough for us to deduce a general algorithm.

For the other 17 strings in your first post, show us what you expect to end up with after they are processed.