topic Reverse group_by concatenation in App Development

Reverse group_by concatenation

pmk — Thu, 29 Oct 2020 11:54:03 GMT

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

Re: Reverse group_by concatenation

GaryGiles — Thu, 29 Oct 2020 13:19:21 GMT

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;

Re: Reverse group_by concatenation

Vegar — Thu, 29 Oct 2020 13:46:27 GMT

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

Re: Reverse group_by concatenation

pmk — Fri, 30 Oct 2020 08:57:54 GMT

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

Thanks a lot!!

@Anonymous: Works as well! Thanks!