Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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....
];
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 :
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 :
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?
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
];
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