How to load Pivot table from excel into qliksense

[Raju_123]

Hi ,

I have requirement to load excel pivot table into qliksense.

The excel has multiple dimensions and which i am facing problem to load.

Can you please suggest how to load into qliksense.

I have attached sample excel for your reference.

[eliran · Accepted solution]

Hi,

You will have to fiddle with the structure of the excel and use crosstable to unpivot your data.

One approach is to load it in two parts, load the 'Actual' columns first, then loading the 'Unpaid' columns.

This is a quick code that helps you achive it

Spoiler

[FY 19 TEMP]:
LOAD
[F1],
[APR 22],
[MAY 22],
[JUN 22],
[JUL 22],
[AUG 22],
[SEP 22],
[OCT 22],
[NOV 22],
[DEC 22],
[JAN 23],
[FEB 23],
[MAR 23]
FROM [lib://AttachedFiles/Sample file.xlsx]
(ooxml, embedded labels, header is 2 lines, table is [FY 19]);

[FY 19]:
NoConcatenate
load * Resident [FY 19 TEMP]
where F1<>'Total' and F1<>'ITEMS' and Len(F1)>0;
drop Table [FY 19 TEMP];

CrossTable(MonthYear, Sales,1)
load * Resident [FY 19];
drop Table [FY 19];

I hope it helps,

Eliran.

[Raju_123]

Hi Eliran,

Many Thanks .

The Solution that you have provided is almost working for my requirement.

But I need Under ITEMS there are FRUITS and VEGETABLES are main Projects. Under fruits, the sub items are apple, orange, pineapple. Under vegetables, the sub item is cucumber.

I need main (FRUITS,VEGETABLES) in one group dynamically (if user input other project it has to change). and sub in another group.

Could You please help on this.