Delimit by Row

[nickmarlborough]

Hi, 

In the load editor i have loaded a table and named it [Table1]:

in table 1, i have the below:

[Table1]:

LOAD

[Names],
SubField([Names], ';') as [Delimited Names],

FROM file 

I now want to take the first letter from the [Delimited Names].... how would i do this?

[steeefan]

One of those should work for you:

// If you need to keep the names as well
[Table1]:
LOAD
  *,
  Left([Delimited Names], 1) AS FirstChar;
LOAD
  [Names],
  SubField([Names], ';') as [Delimited Names]
FROM
  file;

// otherwise
[Table1]:
LOAD
  [Names],
  Left(SubField([Names], ';'), 1) AS FirstChar;
FROM
  file;

[marcus_sommer]

Maybe with:

load *, left([Delimited Names], 1) as FirstLetter;
LOAD [Names], SubField([Names], ';') as [Delimited Names]
FROM file 

[nickmarlborough]

this is very odd...because the delimit of the [Names] field has meant a different name has appeared

i.e. 

Names = Jack;Nick   --->>>> Delimit = Alex

i would hope to have got:

Names = Jack;Nick   --->>>> Delimit = Jack

Names = Jack;Nick   --->>>> Delimit = Nick

It might be worrth noting that the load editor features Qualify and Unqualify so that the data when joined can be associated ONLY on an ID column and not other overlapping field names. 

The end goal would be to have 4 columns:

ID ----- Names-----Delimit Names------first digit

ID 1---Jack;Nick----Jack------J

ID 1----Jack;Nick ---Nick ----- N

ID 2-----Alex -----Alex------A

ID 3-----Jack;Oscar;Sarah ---- Jack ---- J

ID 3 ----Jack;Oscar;Sarah ----- Oscar ---O
 

etc.....

My data looks like this at the moment:

Qualify *;

UnQualify [ID];

[Table 1 ]:

Load

ID

Names

SubField(Text([Names),';') as [Delimit],

Left(SubField(Text([Names]),';'),1) as [first]

from File

Unqualify *;

Why are the Subfield and the Left not working ( i have a feeling the subfield and the overall assocation on the ID is causing a mess... but thats a guess) - any help wouuld be greatly appreciated

[steeefan]

Works for me:

QUALIFY *;
UNQUALIFY ID;

[Table1]:
LOAD
  ID,
  [Names],
  Left(SubField([Names], ';'), 1) AS FirstChar;
LOAD * INLINE [
  ID, Names
  1, Jack;Nick
  2, Alex
  3, Jack;Oscar;Sarah
];

[nickmarlborough]

is there a way to do this without having to insert the names? as i have alot of names! so typing wouldnt work

[steeefan]

This will work with any data source, as done in my earliest post: FROM file. I just did it as an inline load to get your data into my testing app.

[marcus_sommer]

Qualifying is a terrible stuff - creating much more problems as it might be solving. I know only one practically usage which is keeping an intermediate state of data within the data-model to check the evaluation of any transformation, like:

qualify *; x: load * resident Y; unqualify *;

Within a "normal" data-model which should be a star-scheme it won't be needed because each record could get it's source-information, like: 'X' as Source or 'Y' as Source - including the information within the table/field-names isn't necessary.

Therefore I suggest to skip this measure and if any renaming is wanted to apply it explicitly with AS and/or ALIAS and/or a mapping (which might be the last script-step within a report-layer to remain in the entire ETL chain on the origin source-field names).

I'm quite sure that subfield() and left() are working properly and if you get not the expected results that they aren't used correctly. Quite helpful is to track all parts of the transformation, for example with something like:

load *, rowno() as RowNo, left([Delimit],1) as [first];
load ID, Names, trim(subfield([Names],';', iterno())) as [Delimit],
        recno() as RecNo, iterno() as IterNo
from File while iterno() <= substringcount([Names], ';') + 1;

then applying the counter-fields and your ID + Name-Parts within a table-box will show which source-record becomes which target-record and which iteration-records are there.