Reverse group_by concatenation

[pmk]

Hi,

this might have been asked before but I just can't find the solution on the forum (might be a language issue):

I have the following table:

KEY    Value

K1       A1;B2;C3

K2       D4;E5

K3        F6;G7

etc.

Unique keys in the field KEY and concatenated strings in the field Value separated by ';'. Is there an easy way to reverse concatenation to get the following table:

KEY     Value

K1      A1

K1      B2

K1      C3

K2      D4

K2      E5

etc.

Thanks and kind regards

Philipp

[GaryGiles · Accepted solution]

Try the following load script.  You will need to replace TableName with your table name.

newtable:
NoConcatenate
Load * Inline [
tKEY, tValue
];

for k=1 to NoOfRows('TableName')
  Let kKey = Peek('KEY',k-1,'TableName');
  Let kValue = Peek('Value',k-1,'TableName');  
  Let Cnt = SubStringCount('$(kValue)',';');
  for i=1 to $(Cnt)+1
    Concatenate (newtable)
    Load
      '$(kKey)' as tKEY,
      subfield('$(kValue)',';',$(i)) as tValue
    AutoGenerate (1);
  next i;
next k;

[Vegar · Accepted solution]

Hi Phillipp

You should be able to load it like I've done below using subfield().

LOAD [KEY], SubField([Value], ';') as Value

FROM YourSource;

Best regards

Vegar

[pmk]

Oh.....that is really simple. I was convinced that the 3rd argument in SubField was mandatory.

Thanks a lot!!

@Anonymous: Works as well! Thanks!