Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a situation where I am trying to parse data in a flat file that has varying column names.
Simplified example of the data:
Sample 1
A|John|B|123 Lane|C|345|D|765|E|Age30
Sample 2
A|John|B|123 Lane|C|345|E|Age30
I am loading the data like this and it is working for this part:
Load
textbetween(@1,'A|','|C') as Name and Address,
subfield(@1,'E|',2) as Age
From Table;
However, I want to load the data after 'C|', the value of 345 in this case, as a field and if the data after 'D|', but before '|E' exists, I'd like to load that as a field as well, and have it null if it doesn't exist.
Does anyone know how to solve something like this? I'd appreciate any suggestions. Thanks!
Is the | always separator and the format doesn't change? If yes, for getting C value you can use:
subfield(@1,'|',6) as C
and for determining if there is any D value:
if(wildmatch(@1,'|D|'), subfield(@1,'|',8),0) as D
Is the | always separator and the format doesn't change? If yes, for getting C value you can use:
subfield(@1,'|',6) as C
and for determining if there is any D value:
if(wildmatch(@1,'|D|'), subfield(@1,'|',8),0) as D