Split field into multiple fields

[laura_1]

Hi, 

I have a field that contains multiple key values I'd like to split into multiple fields within the same table. The challenge is that I need to do that for multiple tables, and the number of key values to split in each of those tables is different. 

Example data:

table1:

LOAD * INLINE [

key, kpi_name, kpi_value

1|2|3, sales, 1000

4|5|9, sales, 500

10|9|4, profit, 300

1|3|6, profit, 200

];

table2:

LOAD*INLINE[

key, kpi_name, kpi_value

1|4|5|6, visitors, 500

9|0|9|7, visitors, 4000

9|8|8|7, shoppers, 400

];

Desired outcome: 

table1:

key1, key2, key3, kpi_name, kpi_value

1,2,3, sales, 1000

4,5,9, sales, 500

10,9,4, profit, 300

1,3,6, profit, 200

table2:

key1, key2, key3, key4, kpi_name, kpi_value

1,4,5,6, visitors, 500

9,0,9,7, visitors, 4000

9,8,8,7, shoppers, 400

];

A similar problem has been addressed here but it splits the field into multiple tables connected by synthetic keys rather than multiple fields within the same table:

https://community.qlik.com/t5/QlikView-Scripting/Splitting-a-field-into-multiple-fields/td-p/400842

Is there a way to split the key field into multiple fields when the number of values to split is variable?

Thanks for your help,

Laura 

[Taoufiq_Zarra · Accepted solution]

can you test this version?

Table:
/*
LOAD * INLINE [

key, kpi_name, kpi_value

1|2|3, sales, 1000

4|5|9, sales, 500

10|9|4, profit, 300

1|3|6, profit, 200

];
*/

LOAD * INLINE [

key, kpi_name, kpi_value

1|4|5|6, visitors, 500

9|0|9|7, visitors, 4000

9|8|8|7, shoppers, 400

];

TableKey:
load key resident Table;


let Nheader = SubStringCount(Peek('key',1),'|');

for i=1 to $(Nheader)+1

inner join (TableKey)

LOAD *, SubField(key,'|',$(i)) as key$(i)

Resident TableKey ;

NEXT i;

Noconcatenate

TableFinal:

load * resident Table;

join
load * resident TableKey;

drop table Table;
drop table TableKey;

if I use table 1 I get the output:

the same for table 2

Cheers,

[andoryuu]

Hi @laura_1 ,

@Taoufiq_Zarra 's solution is pretty elegant and should solve this issue.  Additionally, is there anything wrong with explicitly creating those key fields on the table?  For example, why not have the tables be loaded thusly (also, i'm assuming your actual tables don't have the same-named fields "kpi_name" and "kpi_value" so that you don't get synthetic keys...)?

table1:

LOAD SubField(key,'|',1) as Key1, SubField(key,'|',2) as Key2, SubField(key,'|',3) as Key3, kpi_name, kpi_value INLINE [

key, kpi_name, kpi_value

1|2|3, sales, 1000

4|5|9, sales, 500

10|9|4, profit, 300

1|3|6, profit, 200

];

table2:

LOAD SubField(key,'|',1) as Key1, SubField(key,'|',2) as Key2, SubField(key,'|',3) as Key3, SubField(key,'|',4) as Key4, kpi_name, kpi_value INLINE[

key, kpi_name, kpi_value

1|4|5|6, visitors, 500

9|0|9|7, visitors, 4000

9|8|8|7, shoppers, 400

];

Here's a pic of what is yielded for table1 for this:

[laura_1]

Worked perfectly, thank you @Taoufiq_Zarra !

[laura_1]

 Hi @andoryuu, 

My actual tables do have kpi_name and kpi_value fields, they go into a concatenated fact table so synthetic keys aren't an issue. 

I have a variable number of key fields in each table so it is not possible to hard code key names into the solution unfortunately.