Opposite crosstable from an existing table

[shahafei2]

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....

];

[Taoufiq_Zarra · Accepted solution]

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 :

[shahafei2]

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?

[Taoufiq_Zarra]

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

];

[shahafei2]

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