Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, I have to transform in script the table
HOw can I do that ?
Thank you in advance
Hi,
Try this script
[Data]:
LOAD * Inline [
Cliente , Mese , Litri1 , Cum
5 , 1 , 300 , 300
5 , 2 , 100 , 400
5 , 3 , 100 , 500
5 , 4 , 600 , 1100
10 , 1 , 600 , 600
10 , 2 , 200 , 800
10 , 3 , 1000 , 1800
10 , 4 , 400 , 2200];
[TMP1]:
GENERIC LOAD Cliente,Mese,Cum RESIDENT [Data];
[RESULT]:
LOAD DISTINCT Cliente RESIDENT [Data];
DROP TABLE [Data];
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;
Regards,
Sokkorn
Hai bisogno di una pivot (la selezioni nei grafici), con due dimensioni, cliente e mese, e con una espressione data dalla somma di cum.
Se vuoi sapere come caricare la tabella , nello script usa file tabellari e cerca la tabella (file xls), la carichi includendo le etichette ed il gioco è fatto.
Se necessiti di ulteriori spiegazioni dimmelo.
try to use generic load like this.
see attachement
Hi,
Try this script
[Data]:
LOAD * Inline [
Cliente , Mese , Litri1 , Cum
5 , 1 , 300 , 300
5 , 2 , 100 , 400
5 , 3 , 100 , 500
5 , 4 , 600 , 1100
10 , 1 , 600 , 600
10 , 2 , 200 , 800
10 , 3 , 1000 , 1800
10 , 4 , 400 , 2200];
[TMP1]:
GENERIC LOAD Cliente,Mese,Cum RESIDENT [Data];
[RESULT]:
LOAD DISTINCT Cliente RESIDENT [Data];
DROP TABLE [Data];
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;
Regards,
Sokkorn
Hi Sokkorn,
I use this script a lot but I am struggling to adapt it when I have more tables than the ones set for transformation.
In my present case I have the number of tables generated by the generic load and the load distinct, plus 6 other tables previously loaded (I really need them to load before).
Can you help me tweak it for this or any other number of cases?
Thank you.
Nuno
Hi Nuno,
You may try to concatenate those table into one then we can transform it in one place.
Regards,
Sokkorn