Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

priqvqlik
Contributor

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

Re: how to combine after generic load

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

7 Replies

Re: how to combine after generic load

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

priqvqlik
Contributor

Re: how to combine after generic load

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.

Re: how to combine after generic load

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
Contributor II

Re: how to combine after generic load

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
Contributor II

Re: how to combine after generic load

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

Thanks!

Balanandam

Re: how to combine after generic load

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
Contributor II

Re: how to combine after generic load

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

Community Browser