Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Transform a table in script

Hi all, I have to transform in script the table

Immagine.png

HOw can I do that ?

Thank you in advance

1 Solution

Accepted Solutions
Sokkorn
Master
Master

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

View solution in original post

5 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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.

Not applicable
Author

try to use generic load like this.

see attachement

Sokkorn
Master
Master

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

Not applicable
Author

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

Sokkorn
Master
Master

Hi Nuno,

You may try to concatenate those table into one then we can transform it in one place.

Regards,

Sokkorn