Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

load data from multi level pivot table.

Hi, I have a pivot table from which i want to load data from. the pivot table have multiple levels so simple crosstable wont work. Is there a way to load data from this type of table. 

I am attaching a sample excel file for reference.

1 Solution

Accepted Solutions
Specialist II
Specialist II

This gets a bit more complicated; basically data needs to be saved to a local QVD and then reloaded to be able to manipulate it properly. Also, I've added a variable that you can see at the beginning of the script (vAttributeFields) where you can define the number of attribute fields (in this case set to 3: Supplier, Customer, Product); if you add more columns just change this number. See application attached; please note the expression editor is highlighting in red a couple of sections of the script (not sure why) but it works fine

SamplePivot.png

 

set vAttributeFields = 3;


let vAttributeFields2 = $(vAttributeFields)+1;

TRACE $(vAttributeFields2);

FirstRow:
LOAD @1 as Metric,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, table is Sheet1, filters(
Transpose(),
Replace(1, top, StrCnd(null))
));


SecondRow:
LOAD @1 as Month,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, header is 1 lines, table is Sheet1, filters(
Transpose(),
Replace(1, top, StrCnd(null))
));


ThirdRow:
LOAD @1 as Type,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, header is 2 lines, table is Sheet1, filters(
Transpose()
));

left join (FirstRow) load * Resident SecondRow;
drop table SecondRow;

left join (FirstRow) load * Resident ThirdRow;
drop table ThirdRow;

Data:
LOAD *,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, header is 3 lines, table is Sheet1, filters(
Transpose()
));

left join (FirstRow) load * Resident Data;
drop table Data;


RENAME Table FirstRow to Data_temp;

 

Data_temp2:
NoConcatenate load * Resident Data_temp where @1 <> '';

drop table Data_temp;

store Data_temp2 into [lib://Data/AttributeDataFields.qvd];


drop table Data_temp2;

 

Attributes:
LOAD *,
RowNo() as RowNumber
FROM
[lib://Data/AttributeDataFields.qvd]
(qvd,embedded labels, filters(
Remove(Col, Pos(Top, 1)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 2)),
Remove(Row, RowCnd(Interval, Pos(Top, $(vAttributeFields2)), Pos(Bottom, 1), Select(1, 0))),
Rotate(right)
));

Data_temp:
CrossTable(RowNumber, Data, 3)
LOAD *
FROM
[lib://Data/AttributeDataFields.qvd]
(qvd, filters(
Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, $(vAttributeFields)), Select(1, 0))),
Remove(Col, Pos(Top, 1))
));

Data:
Load @1 as Metric, @2 as Month, @3 as Type, (num(PurgeChar(RowNumber, '@')) - $(vAttributeFields)) as RowNumber, Data as Value Resident Data_temp;

drop table Data_temp;

left join (Data) load * Resident Attributes;

drop table Attributes;

Drop field RowNumber from Data;

 

View solution in original post

5 Replies
Specialist II
Specialist II

See script below and dashboard attached

SamplePivot.png


FirstRow:
LOAD @1 as Metric,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, table is Sheet1, filters(
Transpose(),
Replace(1, top, StrCnd(null))
));


SecondRow:
LOAD @1 as Month,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, header is 1 lines, table is Sheet1, filters(
Transpose(),
Replace(1, top, StrCnd(null))
));


ThirdRow:
LOAD @1 as Type,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, header is 2 lines, table is Sheet1, filters(
Transpose()
));

left join (FirstRow) load * Resident SecondRow;
drop table SecondRow;

left join (FirstRow) load * Resident ThirdRow;
drop table ThirdRow;

Data:
LOAD *,
RowNo()+1 as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, embedded labels, header is 3 lines, table is Sheet1, filters(
Transpose()
));

left join (FirstRow) load * Resident Data;
drop table Data;


RENAME Table FirstRow to Data_temp;

Data_temp2:
NoConcatenate load * Resident Data_temp where Type <> '';

drop table Data_temp;

drop field RowNumber;

Data:
CrossTable (Product, Value,3) load * Resident Data_temp2;

drop table Data_temp2;

Partner
Partner

Thanks for the solution. It is working perfectly fine but I have multiple columns along with product column such as customer , supplier etc. how should we handle such cases

Specialist II
Specialist II

Hi,

I would like to understand the logic behind your code, could you please share the Excel Data you have used to construct the app.

Also , could you please explain the below line of Code  you have used , I tried to google but did not get any satisfactory results

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

Specialist II
Specialist II

This gets a bit more complicated; basically data needs to be saved to a local QVD and then reloaded to be able to manipulate it properly. Also, I've added a variable that you can see at the beginning of the script (vAttributeFields) where you can define the number of attribute fields (in this case set to 3: Supplier, Customer, Product); if you add more columns just change this number. See application attached; please note the expression editor is highlighting in red a couple of sections of the script (not sure why) but it works fine

SamplePivot.png

 

set vAttributeFields = 3;


let vAttributeFields2 = $(vAttributeFields)+1;

TRACE $(vAttributeFields2);

FirstRow:
LOAD @1 as Metric,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, table is Sheet1, filters(
Transpose(),
Replace(1, top, StrCnd(null))
));


SecondRow:
LOAD @1 as Month,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, header is 1 lines, table is Sheet1, filters(
Transpose(),
Replace(1, top, StrCnd(null))
));


ThirdRow:
LOAD @1 as Type,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, header is 2 lines, table is Sheet1, filters(
Transpose()
));

left join (FirstRow) load * Resident SecondRow;
drop table SecondRow;

left join (FirstRow) load * Resident ThirdRow;
drop table ThirdRow;

Data:
LOAD *,
RowNo() as RowNumber
FROM
[lib://Data/SamplePivot.xlsx]
(ooxml, no labels, header is 3 lines, table is Sheet1, filters(
Transpose()
));

left join (FirstRow) load * Resident Data;
drop table Data;


RENAME Table FirstRow to Data_temp;

 

Data_temp2:
NoConcatenate load * Resident Data_temp where @1 <> '';

drop table Data_temp;

store Data_temp2 into [lib://Data/AttributeDataFields.qvd];


drop table Data_temp2;

 

Attributes:
LOAD *,
RowNo() as RowNumber
FROM
[lib://Data/AttributeDataFields.qvd]
(qvd,embedded labels, filters(
Remove(Col, Pos(Top, 1)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 2)),
Remove(Row, RowCnd(Interval, Pos(Top, $(vAttributeFields2)), Pos(Bottom, 1), Select(1, 0))),
Rotate(right)
));

Data_temp:
CrossTable(RowNumber, Data, 3)
LOAD *
FROM
[lib://Data/AttributeDataFields.qvd]
(qvd, filters(
Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, $(vAttributeFields)), Select(1, 0))),
Remove(Col, Pos(Top, 1))
));

Data:
Load @1 as Metric, @2 as Month, @3 as Type, (num(PurgeChar(RowNumber, '@')) - $(vAttributeFields)) as RowNumber, Data as Value Resident Data_temp;

drop table Data_temp;

left join (Data) load * Resident Attributes;

drop table Attributes;

Drop field RowNumber from Data;

 

View solution in original post

Specialist II
Specialist II

These are functions within the "filters" set of commands you can run when importing data. Transpose does what it says, while replace, in this case, goes through every row from the top and copies the previous value if it finds the cell empty. I normally get these statements from the import wizard in QlikView