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: 
Pragun
Partner - Contributor II
Partner - Contributor II

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

6 Replies
lorenzoconforti
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;

Pragun
Partner - Contributor II
Partner - Contributor II
Author

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

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

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

 

lorenzoconforti
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

rajeshwar1
Partner - Contributor III
Partner - Contributor III

I tried this code and used the excel attached which includes 3 columns like product supplier customer but i am not getting any records. Please look into it.