scripting help to load excel data

[#1]

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

[vgutkovsky]

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

Regards,

Vlad

[Sokkorn · Accepted solution]

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

[#4]

Thanks Guys. It is now working.