topic Transformation steps in Qlikview (Excel File) in QlikView

Transformation steps in Qlikview (Excel File)

Anonymous — Fri, 04 Mar 2016 09:18:30 GMT

Hi All,

Could you please see the attached excel file and how can we transform the data for better understand..

In the excel spread sheet we have years like(2015,2016) below the Quarter and Months ,this needs to be reflected in the dashboard as well.

Regards,

Devendar

Re: Transformation steps in Qlikview (Excel File)

avinashelite — Fri, 04 Mar 2016 11:54:17 GMT

check this :

multi_header_pivot_import.qvw

Re: Transformation steps in Qlikview (Excel File)

-SW- — Fri, 04 Mar 2016 11:55:13 GMT

Hi Lakkyreddy,

I would recommend:

Load the excel sheet using the file wizard.
Use "Enable Transformation"

Within this, the first thing I would do is use the

Rotate Function.

After that, I will fill the empty rows for your years quarter and months, with the

Fill Function

Then Add in the titles for Year, Quarter, Month,

Embed your headers to 1 line.

Load.... This should make your data more understandable for loading into qlikview

If you get confused... heres the code for your file.

LOAD F1 as Year,

F2 as Quarter,

F3 as Month,

[Pre-development enquiry],

Supply,

Sewerage,

[S45 connections],

[S45 application ack.],

[S45 quotation sent out (no. of plots)],

[S41 application ack],

[S41 quotation sent out < 500 plots],

[S41 quotation sent out >500 plots],

[Mains constructed (schemes)],

[Self lay application ack.],

[Sel lay quotations],

[Sel lay connections (no.of plots)],

[Sewer requistion application ack],

[S104/S185 application ack.],

[S104/S185 application reply],

[S104 agreement sent out],

[S106 technical approval],

[S106 rejection],

[Supply total],

[Sewerage total]

FROM

[Developers LoS breakdown by department.xls]

(biff, embedded labels, table is Sheet1$, filters(

Transpose(),

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

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null))

));

Re: Transformation steps in Qlikview (Excel File)

Anonymous — Fri, 04 Mar 2016 11:59:20 GMT

did you check this?

QlikView Load Multiline Header Labels as Field Names from Excel (or CSV) source · GitHub

Re: Transformation steps in Qlikview (Excel File)

sunny_talwar — Fri, 04 Mar 2016 12:13:49 GMT

Does the attached looks like close to what you wanted?

Script:

Table:

CrossTable (Header, Data)

LOAD [Pre-development enquiry],

Volume & '|' & Achieved & '|' & Missed & '|' & [Perf %],

Volume1 & '|' & Achieved1 & '|' & Missed1 & '|' & [Perf %1],

Volume2 & '|' & Achieved2 & '|' & Missed2 & '|' & [Perf %2],

Volume3 & '|' & Achieved3 & '|' & Missed3 & '|' & [Perf %3],

Volume4 & '|' & Achieved4 & '|' & Missed4 & '|' & [Perf %4],

Volume5 & '|' & Achieved5 & '|' & Missed5 & '|' & [Perf %5],

Volume6 & '|' & Achieved6 & '|' & Missed6 & '|' & [Perf %6],

Volume7 & '|' & Achieved7 & '|' & Missed7 & '|' & [Perf %7],

Volume8 & '|' & Achieved8 & '|' & Missed8 & '|' & [Perf %8],

Volume9 & '|' & Achieved9 & '|' & Missed9 & '|' & [Perf %9],

Volume10 & '|' & Achieved10 & '|' & Missed10 & '|' & [Perf %10],

Volume11 & '|' & Achieved11 & '|' & Missed11 & '|' & [Perf %11],

Volume12 & '|' & Achieved12 & '|' & Missed12 & '|' & [Perf %12],

Volume13 & '|' & Achieved13 & '|' & Missed13 & '|' & [Perf %13],

Volume14 & '|' & Achieved14 & '|' & Missed14 & '|' & [Perf %14],

Volume15 & '|' & Achieved15 & '|' & Missed15 & '|' & [Perf %15],

Volume16 & '|' & Achieved16 & '|' & Missed16 & '|' & [Perf %16],

Volume17 & '|' & Achieved17 & '|' & Missed17 & '|' & [Perf %17],

Volume18 & '|' & Achieved18 & '|' & Missed18 & '|' & [Perf %18],

Volume19 & '|' & Achieved19 & '|' & Missed19 & '|' & [Perf %19],

Volume20 & '|' & Achieved20 & '|' & Missed20 & '|' & [Perf %20],

Volume21 & '|' & Achieved21 & '|' & Missed21 & '|' & [Perf %21],

Volume22 & '|' & Achieved22 & '|' & Missed22 & '|' & [Perf %22]

FROM

[Developers LoS breakdown by department.xls]

(biff, embedded labels, header is 3 lines, table is Sheet1$)

Where Len(Trim([Pre-development enquiry])) > 0;

FinalTable:

LOAD [Pre-development enquiry],

SubField(Header, ' & ''|'' & ', 1) as Header1,

SubField(Data, '|', 1) as Volume,

SubField(Data, '|', 2) as Achieved,

SubField(Data, '|', 3) as Missed,

SubField(Data, '|', 4) as [Perf %]

Resident Table;

DROP Table Table;

Left Join (FinalTable)

LOAD *,

MonthName(Date(MakeDate(Year, Month))) as MonthYear;

LOAD F1 as Year,

F2 as Quarter,

Month(Date#(Capitalize(Left(F3, 3)), 'MMM')) as Month,

[Pre-development enquiry] & If((AutoNumber(F3&F1)-1) > 0, (AutoNumber(F3&F1)-1)) as Header1

FROM

[Developers LoS breakdown by department.xls]

(biff, embedded labels, table is Sheet1$, filters(

Transpose(),

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

Replace(2, top, StrCnd(null)),

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

Replace(3, top, StrCnd(null))

))

Where [Pre-development enquiry] = 'Volume';

Re: Transformation steps in Qlikview (Excel File)

Anonymous — Fri, 04 Mar 2016 14:02:19 GMT

Thanks for sharing but year, month, Quarter data is not coming. If i remove left join to final table year, month data showing and remaining data is not displaying..

Re: Transformation steps in Qlikview (Excel File)

sunny_talwar — Fri, 04 Mar 2016 14:04:07 GMT

Won't know what you are doing differently unless I see. Above example seems to be working. Can you share your application?

Re: Transformation steps in Qlikview (Excel File)

settu_periasamy — Fri, 04 Mar 2016 14:56:17 GMT

Hi,

The Alternate way already mentioned by Avinashelite‌ using multi_header_pivot_import.qvw

You just need to change below value only..(Mentioned in Red color)

SET vSourceFile = 'Developers LoS breakdown by department.xls';

SET vSheet = '[Sheet1$]';

SET vType = 'biff';

HDims:
load * inline [
HDimLevel, HFieldName
HDim1, Year
HDim2, Quarter
HDim3, Month
HDim4, Type
];
VDims:
load * inline [
VFieldName
Pre-development enquiry
];

That's it. Then, just format your table (Month, Date, Year ) using Resident..

Re: Transformation steps in Qlikview (Excel File)

sunny_talwar — Fri, 04 Mar 2016 14:59:03 GMT

Wow this is some cool, but complicated stuff. I have never seen this before. Thanks for sharing this Settu

Re: Transformation steps in Qlikview (Excel File)

settu_periasamy — Fri, 04 Mar 2016 15:04:40 GMT

Sunny, I always use this, if the excel source looks like a pivot table.

It is not complicated (if we use often). Already Gysbert did everything on this. Just we need give the input.. 🙂

Re: Transformation steps in Qlikview (Excel File)

Wed, 10 Aug 2016 10:38:09 GMT

Hi Sunny,

Can I ask how you loaded the CrossTable? Did you change the field names in the excel table before loading it in and if so, why did you do that?

Thanks

Re: Transformation steps in Qlikview (Excel File)

sunny_talwar — Wed, 10 Aug 2016 12:53:21 GMT

I did not change anything in the sample data. Just did some manipulation in the script with the names that were picked by the script.