topic Re: Opposite crosstable from an existing table in QlikView

Opposite crosstable from an existing table

shahafei2 — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

All previous post that I found was in order to do the opposite than my request

I would like to crosstable as an existing table

The current data looks like:

Table1:

Load*Inline [

OrderNumber,Zone,Volume

1,A,58

1,B,12

1,C,46

2,A,43

2,C,64

....

10000,E,3

];

My goal is to cross it in a way that I'll have a table that looks like

Cross_Table1:

Load * Inline [

OrderNumber,A,B,C,D,...,E

1,58,12,46...

2,43,,64....

....

10000,,,,,3....

];

Re: Opposite crosstable from an existing table

Taoufiq_Zarra — Wed, 01 Jul 2020 13:40:46 GMT

Hi,

Maye be Generic load :

Table1: Load*Inline [ OrderNumber,Zone,Volume 1,A,58 1,B,12 1,C,46 2,A,43 2,C,64 10000,E,3 ]; Crosstable: Load distinct OrderNumber resident Table1; DATA: Generic LOAD * resident Table1; drop table Table1; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)', 'DATA.*') THEN LEFT JOIN ([Crosstable]) LOAD distinct * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i

output :

Re: Opposite crosstable from an existing table

shahafei2 — Thu, 02 Jul 2020 10:07:45 GMT

Hi Taoufiq ZARRA,

Thank you for your response,

I tried to run it but it is taking too long (I have a few millions of records to record, it's run for almost 12 hours and didn't finish yet )

any other suggestion?

Re: Opposite crosstable from an existing table

Taoufiq_Zarra — Thu, 02 Jul 2020 10:17:24 GMT

the for loop is used to combine the Generic load tables

Can you test without, i.e

Table1: Generic Load * Inline [ OrderNumber,Zone,Volume 1,A,58 1,B,12 1,C,46 2,A,43 2,C,64 10000,E,3 ];

Re: Opposite crosstable from an existing table

shahafei2 — Thu, 02 Jul 2020 10:51:45 GMT

Thank you,

my mistake,

I found out that the reason was that I had a few more attributes in the source table

so the "*" did not work for me

I replaced it with the relevant attributes only and it runs in less than a minute