how to combine after generic load

[qvqfqlik]

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

[sunny_talwar · Accepted solution]

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;

[qvqfqlik]

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]

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]

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]

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

[rajasekhar6067]

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

[vishalmanu]

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.