multi loading

[FrankGrimm]

Hello together,

i have a datasource with this structure.

and i need this result:

I worked with

SubStringCount(Places,'#') to count how often i have an item for each person.

With TextBetween(Places,'#','/', X) i can select the item with the number X. 

But how can i build a for..next  oder while loop to read all values to get the result i need?

Thanks to all.

Frank

[Or]

Try using Subfield() - it does exactly what you want, I believe.

[FrankGrimm]

Hey Or,

thanks for your answer.

i think subfiles can deliver the single items of the postion 1 / 2 / 3 , but how can i trigger that every combination of name and place gets a new column in the target table?

[sidhiq91]

@FrankGrimm  Please see the script below using in the back end:

NoConcatenate
Temp:
Load Name,
SubField(Places,'/,') as New_Place

inline [
Name| Places
Frank| #Washington/,#Newyork/,#Dallas/
John| #Wells/,#Kennebunk/
Lisa| #Lancaster/,#tarrytown/,#New Heaven/,#Boston/
] (delimiter is '|');

NoConcatenate
Temp1:
Load *,
replace(New_Place,'#','') as New_Place2
Resident Temp;

NoConcatenate
Temp2:
Load Name,
replace(New_Place2,'/','') as New_Place

Resident Temp1;

Drop table Temp,Temp1;

Exit Script;

If this resolves your issue, please like and accept it as a solution.

[MarcoWedel · Accepted solution]

you mean a new row?

The 2 parameter version of the suggested SubField() function does exactly that:

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions....

Another solution might be however:

table1:
LOAD Name,
     TextBetween(Places,'#','/',IterNo()) as Place
Inline [
Name	Places
Frank	#Washington/,#New York/,#Dalles/
John	#Wells/,#Kennebunk/
Lisa	#Lancaster/,#Tarrytown/,#New Heaven/,#Boston/
] (delimiter is '\t')
While IterNo() <= SubStringCount(Places,'#');

hope this helps

Marco

[FrankGrimm]

Hallo Marco,

super danke. Das war die Lösung.

Grüße

[MarcoWedel]

Immer gerne.
Schön, dass es geholfen hat.