Script

[#1]

Hi

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

Thanks

[#2]

Generally you'll want to use the source data of those tables in Qlikview to construct your own Pivot tables again in Qlikview. Is that not an option?

[#3]

I will get data in this format

[Peter_Cammaert]

There is no easy way to do this (and it doesn't make much sense since you cannot un-aggregate these values anyway).

The big question: is your Excel data always formatted exactly like this? No variations, extensions, omissions, ever? Then it might be possible to get this data into an internal table. The required script code will be extensive and close to unmaintainable.

If not, then try to get access to the source data, as Coen suggested. Will make the job a lot easier. IMHO parsing and dissecting complex dynamically-changing Excel pivot layouts in QlikView script isn't worth the money. 

[jagan]

Hi,

If possible get data from the source instead of excel files.  If not possible try to use this script

Temp:

CrossTable(Month, Value, 5)

LOAD If(Len(Trim(Category)) = 0, Previous(Category), Category) AS Category_New,

     If(Len(Trim(Metrics)) = 0, Previous(Metrics), Metrics) AS Metrics_New,

    *

FROM

[Sample Data.xlsx]

(ooxml, embedded labels, header is 2 lines, table is Sheet1);

Data:

LOAD

Category_New,

Metrics_New,

F3 AS SNo,

MonthName(Num#(Month)) AS Month,

Value,

'INDIA' AS Country

RESIDENT Temp;

DROP TABLE Temp;

Regards,

jagan.

[#6]

Hi,

If I select 'Department' under Category_New am getting only two values (Marketing, Production). Actually I have three values(Marketing, Production, Manufacturing)

Thanks

[settu_periasamy]

One more way may be like this..

Directory;
T1:
CrossTable(Date, Data, 3)
LOAD Category,
Metrics,
Num(F3) as S.No,
[42278.000000],
[42309.000000],
[42339.000000],
[42370.000000]
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,[S.No],MonthName(Num#(Date)) AS Month,Data Resident T1;
DROP Table T1;

[jagan · Accepted solution]

Hi,

Try like this, it will pick the new months dynamically

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;

[#9]

Thank You Jagan & Settu

[#10]

Hi,

I have total 2 countries with same format. Only Country Name will change. How to add another country? Please advise