topic load data from multi level pivot table. in App Development

load data from multi level pivot table.

Pragun — Fri, 17 Jan 2020 12:51:25 GMT

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.

Re: load data from multi level pivot table.

lorenzoconforti — Fri, 17 Jan 2020 16:52:12 GMT

See script below and dashboard attached

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;

Re: load data from multi level pivot table.

Pragun — Mon, 20 Jan 2020 06:03:21 GMT

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

Re: load data from multi level pivot table.

anushree1 — Mon, 20 Jan 2020 07:23:53 GMT

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

Re: load data from multi level pivot table.

lorenzoconforti — Mon, 20 Jan 2020 13:48:42 GMT

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

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;

Re: load data from multi level pivot table.

lorenzoconforti — Mon, 20 Jan 2020 13:53:07 GMT

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

Re: load data from multi level pivot table.

rajeshwar1 — Fri, 26 Jan 2024 00:03:07 GMT

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.