Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to upload Excel Pivot which is grouped at Multilevel

Hi All,

I have a sample pivoted excel file, and want to read it in QlikView and need to do reporting on the same.

Sample pic of data is given below and also attached the same here in this post.

  

MODELNORTHSOUTHHOIndia
DELGGNNorthHYDBNGSouthOEMKOELUIL
EP1000N12440233603180001196
EX 2400103631563000129
EX 2400 S20134205900098
EU 30 iN 6059440184000318
EU 30 isN 241461640216000326
EU70isN25046745147000227
Total Genset 705602672209870002294
000
LM - HRJ 196 401500200060
LM - HRJ 216 118482000045
B.C. - UMK 435 U2ST 001000000010
B.C. - UMK 425 U2ST 00612105200085
B.C. - UMK 435 UEDT 007001000017

I have data like mentioned above....

Can you please let me know how to load it in qlikview and do dashboards based on it.

you can please refer sample data which is attached with this post.

Thanks,

Qliker

4 Replies
qliknerd
Contributor III
Contributor III

Hi Ravi

You can use the CrossTable function to flatten all the columns. If you want the specific groupings to remain, might be easier to create as inline tables and load against the flattened data. Something like this:

Regions:

LOAD * INLINE [

  AREA, REGION

  DEL, NORTH

  JK, NORTH

  WUP, NORTH

  GGN, NORTH

  CHD, NORTH

  JPR, NORTH

  LKO, NORTH

  PAT, EAST

  RAN, EAST

  BBSR, EAST

  CAL, EAST

  GUW, EAST

  BPL, WEST

  AHM, WEST

  MUM, WEST

  HYD, SOUTH

  MDS, SOUTH

  COC, SOUTH

  BNG, SOUTH

  OEM, HO

  KOEL, HO

  UIL, HO

];

Models:

LOAD * INLINE [

  MODEL, MODELTYPE, MODELGROUP

  EP1000N, Genset,

  EX 2400, Genset,

  EX 2400 S, Genset,

  EU 30 iN, Genset,

  EU 30 isN, Genset,

  EU70isN, Genset,

  LM - HRJ 196, C.B.U.,

  LM - HRJ 216, C.B.U.,

  B.C. - UMK 435 U2ST, C.B.U.,

  B.C. - UMK 425 U2ST, C.B.U.,

  B.C. - UMK 435 UEDT, C.B.U.,

  B.C. - UMK 435T U2NT, C.B.U.,

  B.C. - UMK 435T UENT, C.B.U.,

  TLR - FJ 500, C.B.U.,

  TLR - F 300, C.B.U.,

  WJR 2525 GCD, C.B.U.,

  GK 100, ENGINE SALES - (HSPP), A.1. G ENGINE

  GK 200, ENGINE SALES - (HSPP), A.1. G ENGINE

  G 200, ENGINE SALES - (HSPP), A.1. G ENGINE

  GK200(W), ENGINE SALES - (HSPP), A.1. G ENGINE

  G 300, ENGINE SALES - (HSPP), A.1. G ENGINE

  GK 300 Q, ENGINE SALES - (HSPP), A.1. G ENGINE

  GX 80, ENGINE SALES - (HSPP), A.2. G ENGINE

  GX 160D QX (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

  GX 160D QTB (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

  GX 160D QKA, ENGINE SALES - (HSPP), A.2. G ENGINE

  GX 200D QX (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

  GX 200D QTB (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

  GX 200D SD (DLR + OEM), ENGINE SALES - (HSPP), A.2. G ENGINE

  GX 160D LHB3 (LTOB), ENGINE SALES - (HSPP), GX ENGINE - LTOB

  GX 160D LKA, ENGINE SALES - (HSPP), GX ENGINE - LTOB

  WBK 15, PUMPS,

  WBK 30, PUMPS,

  WBK 30FF, PUMPS,

  WB 30X, PUMPS,

  WSK 2020, PUMPS,

  WMK 2520, PUMPS,

  WM 2520X, PUMPS,

  WMK 3025, PUMPS,

  WM 3025X, PUMPS,

  WMK 3025(3), PUMPS,

  WPK-20FF, PUMPS,

  WB 15 X, PUMPS,

  WS 20 X, PUMPS,

  WV30 D, PUMPS,

];

Sales:

CrossTable(AREA, VOLUME, 1)

LOAD

     F2 as MODEL,

     DEL,

     JK,

     WUP,

     GGN,

     CHD,

     JPR,

     LKO,

     //North,

     PAT,

     RAN,

     BBSR,

     CAL,

     GUW,

     //East,

     BPL,

     AHM,

     MUM,

     //West,

     HYD,

     MDS,

     COC,

     BNG,

     //South,

     OEM,

     KOEL,

     UIL //,

     //India

FROM

[https://community.qlik.com/servlet/JiveServlet/download/1555598-340319/Sample%20Sale%20Data.xlsx]

(ooxml, embedded labels, header is 3 lines, table is APR)

Where Not Match(F2, 'GRAND TOTAL','SUB TOTAL','TOTAL','TOTAL (DOM)','Total Genset','C.B.U.','TTL - G ENGINES','TTL - GX ENGINES','PUMPS','M - means this month');

! !
Anonymous
Not applicable
Author

Hi Ian Tsang,


You created this manually right!!!

But for every upload we need to go manual intervention for data update in qlikview, that is not good and data may miss/wrong while entering. and need manpower aswell time taken process.

Thank you for approach, but still looking for as simple reloading way without touching qlikview App.

Thanks,

Ravi

qliknerd
Contributor III
Contributor III

No problem - why would you need manual intervention? The Inline tables are created once to define the dimension groups needed for subtotalling purposes. If the columns and rows are likely to change depending on the source file (e.g. different models, areas or regions), then you could load all columns and rows. However, you will bring in the total/subtotal columns and rows that still have to be dealt with.

Sales:

CrossTable(AREA, VOLUME, 1)

LOAD

     F2 as MODEL,

     *

FROM

[https://community.qlik.com/servlet/JiveServlet/download/1555598-340319/Sample%20Sale%20Data.xlsx]

(ooxml, embedded labels, header is 3 lines, table is APR)

;

Anonymous
Not applicable
Author

Hi Ian,

Sorry for inconvenience, can you please tell me how to do that, and when user do the changes in excel file, it has to pic the same excel file data to qlikview...

Can you please provide steps that you followed as a layman understanding purpose.

Thanks,

Ravi