Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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
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)) :
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;
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
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.