topic Re: Excel script transform file in QlikView

Excel script transform file

Mon, 03 Oct 2011 08:50:56 GMT

Hi everyone,

I'm trying to load a table from excel without having to modify QlikView from outside.

I know it can transform from loading tables but can not. I've looked at the reference manual but it is nothing.

Let's see if anyone can lend a hand.

The example of excel file.

Thanks.

Re: Excel script transform file

lironbaram — Mon, 03 Oct 2011 09:04:19 GMT

hei check this example

hope its helps you

Excel script transform file

Mon, 03 Oct 2011 09:25:34 GMT

Hi,

how did you do?

I tried to do with the cross table but I could not, can you tell me a little guide?

Thank you very much

Re: Excel script transform file

martinpedersen — Mon, 03 Oct 2011 09:26:12 GMT

I think you should use "Crosstable" to allow you transform the data durring the load script:

(First I have renamed column A in the excel sheet to Date but could have used the "@1" instead). Then the load script should be something like:

CrossTable(Terminal, Sales, 1)

LOAD Date(Date, 'dd-mm-yyyy') as Date,

[1],

[2],

[3],

[4],

[5],

[6],

[7],

[8],

[9],

[10],

[11],

[12]

FROM

[..Libro1.xlsx]

(ooxml, embedded labels, table is ventas);

To explain the crosstable function you can use the help function in the script editor. But in this exampe I configured it to have all the columns included in the field "Terminal", and the values as Sales. The 1 is refering to that the first column should be a field - and not included in the cross table. Hope that will do the trick for you...