Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qvqfqlik
Creator
Creator

how to combine after generic load

I have need to have column values as column names, and now I want to combine into single table. How can I achieve this. Please help

ID   NAME,      VALUE

1       name1     val1

1      name2      val2

2      name1      val3

2      name2      val4

3      name1      val5

4     name2       val6

3     name1      val7

4     name2        val8

5     name3       val9


I have done a genric load and got separate tables , but I would need to get like below


ID     name1     name2      name3

1          val1         val2

2       val3            val4    

3       val5          val6                     

4      val7             val8  

5                                           val9

1 Solution

Accepted Solutions
sunny_talwar

If I may, I would like to suggest an alternative to Generic load...

Table:

LOAD * INLINE [

    ID, NAME, VALUE

    1, name1, val1

    1, name2, val2

    2, name1, val3

    2, name2, val4

    3, name1, val5

    3, name2, val6

    4, name1, val7

    4, name2, val8

    5, name3, val9

];

FinalTable:

LOAD Distinct ID

Resident Table;

FOR i = 1 to FieldValueCount('NAME')

LET vField = FieldValue('NAME', $(i));

Left Join (FinalTable)

LOAD ID,

VALUE as [$(vField)]

Resident Table

Where NAME = '$(vField)';

NEXT i

DROP Table Table;

Capture.PNG

View solution in original post

10 Replies
sunny_talwar

If I may, I would like to suggest an alternative to Generic load...

Table:

LOAD * INLINE [

    ID, NAME, VALUE

    1, name1, val1

    1, name2, val2

    2, name1, val3

    2, name2, val4

    3, name1, val5

    3, name2, val6

    4, name1, val7

    4, name2, val8

    5, name3, val9

];

FinalTable:

LOAD Distinct ID

Resident Table;

FOR i = 1 to FieldValueCount('NAME')

LET vField = FieldValue('NAME', $(i));

Left Join (FinalTable)

LOAD ID,

VALUE as [$(vField)]

Resident Table

Where NAME = '$(vField)';

NEXT i

DROP Table Table;

Capture.PNG

qvqfqlik
Creator
Creator
Author

Thanks very much Sunny! You really saved me

But the load time is more for more data, is there a way to reduce load time?. Otherwise this works for me.

sunny_talwar

I don't think you can.... but that is not just the problem with the solution above, I think this will be the same problem you will face when you try to combine the tables created by Generic load....

balanandam
Creator II
Creator II

I'm trying to impliment same one, I've around 70 Names like in this examples to convert as columns, But, my system got stuck after loading 30 names data. Could you let us know if there some thing to be tuned ?

Thanks!

Balanandam

balanandam
Creator II
Creator II

I could see, system resources CPU & RAM being at high utilization when reloading this piece

Thanks!

Balanandam

sunny_talwar

The only thing I would check is to make sure that you are not doing a many to many join..... that is why I used DISTINCT here

FinalTable:

LOAD Distinct ID

Resident Table;

Because If I don't... then I will be loading the same ids multiple times and then joining to those multiple times. So, things like this need to be checked

balanandam
Creator II
Creator II

Excellent! exactly the same issue. Thank you! Sunny.

rajasekhar6067
Contributor II
Contributor II

You are the man sunny.. it worked perfect.. smart coding..

vishalmanu
Partner - Creator
Partner - Creator

Hello Sunny,

I tried the method that you mentioned above, but its now working for me. and i used the script as is like you have mentined in your response.