How to convert values of a Column into Columns separated by ';'.

[akmal_ETG]

Hi all,

I am working with a data where I have a field in which values are nothing but columns name and their values as below:

|      col1      |                col2                         |

|+++++++++|+++++++++++++++++++++++|

|        1         |  col3:-a1; col4:-a2; col5:-a3   |

|        2         | col3:-b1; col4:-b2; col5:-b3    |

I tried using SubField(Col3, ';') as field but this will not fulfill my requirement.

I need final table as below:

|      col1      |        col2           |        col3           |        col4           |

|+++++++++|++++++++++++|++++++++++++|++++++++++++|

|        1        |            a1          |           a2          |           a3           |

|        2        |            b1          |           b2          |           b3           |

Thank You all in advance.

[rafaelencinas · Accepted solution]

Hi @akmal_ETG 

Try this !

Table1:
Load * INLINE [
col1 | col2
1 | col3:-a1; col4:-a2; col5:-a3
2 | col3:-b1; col4:-b2; col5:-b3
](delimiter is '|');

Table2:
NoConcatenate
Load *
,trim(Subfield(col2,';')) as teste1
Resident Table1;

Left Join (Table2)
Load col1
,teste1 as col3
Resident Table2
Where WildMatch(teste1,'*col3*');
;

Left Join (Table2)
Load col1
,teste1 as col4
Resident Table2
Where WildMatch(teste1,'*col4*');
;

Left Join (Table2)
Load col1
,teste1 as col5
Resident Table2
Where WildMatch(teste1,'*col5*');
;

FinalTable:
NoConcatenate
Load
Distinct
col1
,Replace(col3,'col3:-','') as col3
,Replace(col4,'col4:-','') as col4
,Replace(col5,'col5:-','') as col5
Resident Table2;

Drop table Table1;
Drop table Table2;

Bye !

[akmal_ETG]

Thanks for your help @rafaelencinas, I will try this and will update.

[marcus_sommer · Accepted solution]

A more generic approach would be to use a generic load, like:

t1:
load *, subfield(col0, ':-', 1) as Col, subfield(col0, ':-', 2) as Value;
load *, trim(subfield(col2, ';')) as col0;
load * inline [
col1, col2
1, col3:-a1; col4:-a2; col5:-a3
2, col3:-b1; col4:-b2; col5:-b3
];

t2: generic load col1, Col, Value resident t1;

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

Let vTableName = TableName($(vTableNo)) ;

If Subfield(vTableName,'.',1)='t2' Then

Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

End If

Next vTableNo

CombinedGenericTable:

Load distinct col1 resident t1;

For each vTableName in $(vListOfTables)

Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

Drop Table [$(vTableName)];

Next vTableName

drop tables t1;

whereby the generic part is directly taken from:

The Generic Load - Qlik Community - 1473470

[akmal_ETG]

@marcus_sommer Great!,

This solve my actual problem. Thanks a lot