Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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

Tags (3)
1 Solution

Accepted Solutions
Sokkorn
Honored Contributor

Re: Transform a table in script

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

Re: Transform a table in script

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

Re: Transform a table in script

try to use generic load like this.

see attachement

Sokkorn
Honored Contributor

Re: Transform a table in script

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

Not applicable

Re: Transform a table in script

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
Honored Contributor

Re: Transform a table in script

Hi Nuno,

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

Regards,

Sokkorn