Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello i need to create a Table structured like this:
Year | Metal | Kg |
---|---|---|
2000 | Copper | 17 |
2000 | Silver | 14 |
2000 | Gold | 25 |
2001 | Copper | 20 |
2001 | Silver | 21 |
2001 | Gold | 22 |
From a csv with this data inside:
Year | Copper | Silver | Gold |
---|---|---|---|
2000 | 17 | 14 | 25 |
2001 | 20 | 21 | 22 |
Anybody Knows how it can be made using cross tables?
Thank you
Marco
One way is to solve it in the script. You can do this by loading the file three times, one for each metal.
See attached example.
You can do it in 1 load
CrossTable(METAL,KG)
LOAD Year,
Copper,
Silver,
Gold
FROM
Test1.xls
(biff, embedded labels, table is [Sheet1$]);
See Attached:
That is a better crosstable, Ori.
Johan
Thanks
PFA
Is there any other way to get the same result without using single load statements?
Thank you
Marco
Look at Ori's example.
I've seen the post although i've not understood well the CrossTable command's logic:
CrossTable(METAL,KG)
LOAD Year,
Copper,
Silver,
Gold
FROM
Test1.xls
(biff, embedded labels, table is [Sheet1$]);
In particular can you explain how the bold command works to generate the final data?
Thank you
Marco
Hi Marco,
Let try script below:
[DS]:
LOAD * Inline [
Year , Metal , Kg
2000 , Copper , 17
2000 , Silver , 14
2000 , Gold , 25
2001 , Copper , 20
2001 , Silver , 21
2001 , Gold , 22];
[TMP1]:
GENERIC LOAD * RESIDENT [DS];
[RESULT]:
LOAD DISTINCT Year RESIDENT [DS];
DROP TABLE [DS];
FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) AS Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'TMP1.*');
NEXT i
FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i
drop table TableList;
See sample attached file also.
Regards,
Sokkorn