topic Re: Transform a table in script in QlikView

Transform a table in script

Wed, 03 Jul 2013 10:13:11 GMT

Hi all, I have to transform in script the table

HOw can I do that ?

Thank you in advance

Re: Transform a table in script

alexandros17 — Wed, 03 Jul 2013 10:19:56 GMT

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.

Re: Transform a table in script

Wed, 03 Jul 2013 10:30:16 GMT

try to use generic load like this.

see attachement

Re: Transform a table in script

Sokkorn — Wed, 03 Jul 2013 10:30:53 GMT

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

Re: Transform a table in script

Tue, 30 Jul 2013 00:18:12 GMT

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

Re: Transform a table in script

Sokkorn — Tue, 30 Jul 2013 01:05:54 GMT

Hi Nuno,

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

Regards,

Sokkorn