topic How to convert values of a Column into Columns separated by ';'. in App Development

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

akmal_ETG — Thu, 26 Jun 2025 09:11:02 GMT

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.

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

rafaelencinas — Thu, 26 Jun 2025 12:00:02 GMT

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 !

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

akmal_ETG — Thu, 26 Jun 2025 12:04:42 GMT

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

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

marcus_sommer — Thu, 26 Jun 2025 12:22:30 GMT

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

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

akmal_ETG — Fri, 27 Jun 2025 09:46:47 GMT

@marcus_sommer Great!,

This solve my actual problem. Thanks a lot