topic Re: scripting help to load excel data in QlikView

scripting help to load excel data

Fri, 14 Oct 2011 15:00:18 GMT

Hi,

I have attached an excel sheet with a cross table excel data. I would like to use the crosstab feature in QlikView scripting to load this data into a table with the following structure in QlikView.

Table1:

Country

Month

Premium

Matched

% Matched

Could you please help me with this?

Regards,

Murali

Re: scripting help to load excel data

vgutkovsky — Fri, 14 Oct 2011 19:49:41 GMT

See attached--this is a somewhat complicated load because the Excel file is not optimized for use with QlikView.

Regards,

Vlad

Re: scripting help to load excel data

Sokkorn — Sat, 15 Oct 2011 02:03:13 GMT

Hi Murali,

Try this script

[tmp]:
CrossTable(Premium, Data)
LOAD Country, 
     Premium, 
     Matched, 
     [% Matched], 
     Premium1, 
     Matched1, 
     [% Matched1], 
     Premium2, 
     Matched2, 
     [% Matched2]
FROM
Test.xls
(biff, embedded labels, header is 2 lines, table is Sheet1$);
[Data]:
LOAD
    Country,
    IF(ISNULL(RIGHT(Premium,1)+1),1,RIGHT(Premium,1)+1)    AS [MonthNum],
    DUAL(APPLYMAP('MonthMap', IF(ISNULL(RIGHT(Premium,1)+1),1,RIGHT(Premium,1)+1)),IF(ISNULL(RIGHT(Premium,1)+1),1,RIGHT(Premium,1)+1)) AS [MonthName],
    Premium,
    Data
RESIDENT [tmp];
DROP TABLE [tmp];

See the sample attached file.

Let me know, if this help you.

Regards,

Sokkorn

scripting help to load excel data

Mon, 17 Oct 2011 12:16:04 GMT

Thanks Guys. It is now working.