Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
pmk
Contributor
Contributor

Reverse group_by concatenation

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

2 Solutions

Accepted Solutions
GaryGiles
Specialist
Specialist

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;

View solution in original post

Vegar
MVP
MVP

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

View solution in original post

3 Replies
GaryGiles
Specialist
Specialist

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
MVP
MVP

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
Contributor
Contributor
Author

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

Thanks a lot!!

@Gary: Works as well! Thanks!