Transformation steps in Qlikview (Excel File)

[Anonymous]

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

[avinashelite]

check this :

multi_header_pivot_import.qvw

[shun_wong · Accepted solution]

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))

));

[Anonymous]

did you check this?

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

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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

[settu_periasamy]

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

[sunny_talwar]

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

[settu_periasamy]

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