topic Re: Script in QlikView

Script

Wed, 02 Mar 2016 12:02:05 GMT

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

Thanks

Re: Script

Wed, 02 Mar 2016 15:41:44 GMT

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?

Re: Script

Wed, 02 Mar 2016 22:45:29 GMT

I will get data in this format

Re: Script

Peter_Cammaert — Thu, 03 Mar 2016 00:04:59 GMT

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.

Re: Script

jagan — Thu, 03 Mar 2016 01:22:35 GMT

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.

Re: Script

Thu, 03 Mar 2016 02:30:04 GMT

Hi,

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

Thanks

Re: Script

settu_periasamy — Thu, 03 Mar 2016 02:44:17 GMT

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;

Re: Script

jagan — Thu, 03 Mar 2016 03:11:52 GMT

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(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;

Re: Script

Thu, 03 Mar 2016 03:56:37 GMT

Thank You Jagan & Settu

Re: Script

Thu, 03 Mar 2016 06:55:16 GMT

Hi,

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

Re: Script

jagan — Thu, 03 Mar 2016 06:57:50 GMT

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(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(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.

Re: Script

Thu, 03 Mar 2016 07:11:31 GMT

Please find attached sample file.

First two sheets for India and second two sheets for USA

Re: Script

jagan — Thu, 03 Mar 2016 08:06:04 GMT

Hi,

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

Regards,

Jagan.

Re: Script

Thu, 03 Mar 2016 08:47:09 GMT

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"

Re: Script

jagan — Thu, 03 Mar 2016 09:04:38 GMT

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.

Re: Script

settu_periasamy — Thu, 03 Mar 2016 09:21:15 GMT

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;