Script

[#1]

Hi

I got data in Pivot Table. How to load into Qlikview and format. Please find sample report attached

Thanks

[jagan]

Hi,

Try like this

T1:

CrossTable(Date, Data, 3)

LOAD *

FROM

[Sample Data.xlsx]

(ooxml, embedded labels, header is 2 lines, table is Sheet1, filters(

Replace(1, top, StrCnd(null)),

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null))

));

Final:

LOAD 'INDIA' as Country,Category,Metrics, F3 AS [S.No],MonthName(Num#(Date)) AS Month,Data Resident T1;

DROP Table T1;

Country2:

CrossTable(Date, Data, 3)

LOAD *

FROM

[Country.xlsx]

(ooxml, embedded labels, header is 2 lines, table is Sheet1, filters(

Replace(1, top, StrCnd(null)),

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null))

));

Concatenate(Final)

LOAD 'INDIA' as Country,Category,Metrics, F3 AS [S.No],MonthName(Num#(Date)) AS Month,Data Resident Country2;

DROP Table Country2;

Hope this helps you.

Regards,

jagan.

[#3]

Please find attached sample file.

First two sheets for India and second two sheets for USA

[jagan]

Hi,

Use the same script for all 4 sheets and concatenate all the data

Regards,

Jagan.

[#5]

Hi,

Month Format is not matching. I have 'DD/MM/YYYY' format and need to join with other table. It contains 'MMM-YY' format. Here in this script we are taking like this

" MonthName(Num#(Date)) AS Month ". But it is not mapping with other table. How to change the format. I need to convert this into "MMM-YY"

[jagan]

Hi,

Convert Month format to the required by using Date#() like below

Date(Date#(FieldName, 'DD/MM/YYYY), 'MMM-YY')

Hope this helps you.

Regards,

Jagan.

[settu_periasamy]

Hi,

Try this with loop

JDIRECTORY;

For Each vFile in FileList('Sample Data.xlsx')

ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets:
SQLTABLES;
DISCONNECT;
For i = 0 To NoOfRows('Sheets')-1
Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);   

Country:
First 1
LOAD 
D as Country
FROM
[$(vFile)]
(ooxml, no labels, header is 1 lines, table is $(vSheet));
Let vCountry=Peek('Country');

DROP Table Country;

Temp:
CrossTable(Date, Data, 4)
LOAD '$(vCountry)' as Country,*
FROM
[$(vFile)]
(ooxml, embedded labels, header is 2 lines, table is $(vSheet), filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null))
));
Next;
DROP Table Sheets;

Final:
LOAD Country,Category,Metrics,Num(F3) as [S.No],MonthName(Num#(Date)) AS Month,Data Resident Temp;

DROP Table Temp;