topic Re: Splitting a field into multiple fields in QlikView

Splitting a field into multiple fields

Anonymous — Wed, 26 Oct 2016 09:11:16 GMT

Hi,

I have one query in this post.

How can I use variable in this part for i=1 to 5 of the script?.. I want to make it for i =1 to max(test)... test is field in Tab2.

Tab2:

LOAD *,SubStringCount(Change1,'|') as test Inline [

Team1,Change1

A,10|20|30|40|50

];

for i=1 to 5

Tab23:

LOAD *, SubField(Change1,'|',$(i)) as fieldname$(i)

Resident Tab2 ;

NEXT i;

Re: Splitting a field into multiple fields

marcus_sommer — Wed, 26 Oct 2016 15:53:45 GMT

If you used subfield within a load without the third parameter qlik will be automatically loop through them - this meant you don't need your extra outside loop.

- Marcus

Re: Splitting a field into multiple fields

sunny_talwar — Wed, 26 Oct 2016 15:54:21 GMT

Maybe Generic load once you have the data in different rows?

The Generic Load

Re: Splitting a field into multiple fields

Anonymous — Thu, 27 Oct 2016 07:44:11 GMT

Is it possible in some way for i =1 to max(test).

I tried several combinations of Subfield, Concat. I am not getting what I need. With the script above I am getting the right result but If I am able to put max the solution will become dynamic which is required.

Thanks

SAK

Re: Splitting a field into multiple fields

sunny_talwar — Thu, 27 Oct 2016 15:55:27 GMT

Are you trying to get this?

Tab2:

LOAD * Inline [

Team1,Change1

A,10|20|30|40|50

];

FinalTable:

LOAD Distinct Team1

Resident Tab2;

FOR i = 1 to SubStringCount(FieldValue('Change1', 1), '|') + 1

Left Join (FinalTable)

LOAD Team1,

SubField(Change1, '|', $(i)) as FieldName$(i)

Resident Tab2;

NEXT

DROP Table Tab2;