Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Team -
i am having a hard time in processing a cross tab which is in a financial statement format.
In the transformation step in qlikview while importing i am unable to generate into a tabular format i.e in simple rows and columns
Example:
Column A should be repeated for each row .
Column B should be repeated for each row.
Column C is blank and should NOT eliminated
Column D Row 4 i.e Months (Apr to Mar) should be repeated for each row.
The rest should be measures i.e values against each corresponding cells.
Note: Row #14 is in %. How to processing this.
Kindly advice.
Rgds,
Sean.
I think what you want is to transpose the table
If this is not what you are looking for, can you create a table in your Excel file to clarify what you are looking for?
This is the code that generates the table below:
Data:
LOAD Particulars,
[Interest To be Earned],
[Income on Interest],
[Total Income],
Incentives,
[Other Incentives],
[Total Incentives],
Assets,
EOP,
#Customers
FROM
CrossTabFileProcessing.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(
Transpose(),
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 8)),
Remove(Row, Pos(Top, 2))
));
See below; do you actually need the dummy values? they are just null in the source table; in my example below they are excluded (as they are nulls in the excel file)
Data:
CrossTable (Month, Data, 2) Load * where not (Particulars = 'Total Income' or Particulars = 'Total Incentives') ;
LOAD F1 as ItemNo,
Particulars,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec,
Jan,
Feb,
Mar
FROM
CrossTabFileProcessing.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
I think what you want is to transpose the table
If this is not what you are looking for, can you create a table in your Excel file to clarify what you are looking for?
This is the code that generates the table below:
Data:
LOAD Particulars,
[Interest To be Earned],
[Income on Interest],
[Total Income],
Incentives,
[Other Incentives],
[Total Incentives],
Assets,
EOP,
#Customers
FROM
CrossTabFileProcessing.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(
Transpose(),
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 8)),
Remove(Row, Pos(Top, 2))
));
thank you Loren.
The expected output is attached.
It should be simple flat table with,
ItemNo,Particulars,Month, Data
Where Item No is serial no, Month represents (apr, may, jun etc), data represents metric values.
I also want to ignore "Total Income", "Total Incentives" if possible.
Rgds,
Sean.
See below; do you actually need the dummy values? they are just null in the source table; in my example below they are excluded (as they are nulls in the excel file)
Data:
CrossTable (Month, Data, 2) Load * where not (Particulars = 'Total Income' or Particulars = 'Total Incentives') ;
LOAD F1 as ItemNo,
Particulars,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec,
Jan,
Feb,
Mar
FROM
CrossTabFileProcessing.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Just one additional post from the Design Blog area that may be helpful:
https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083
Here is the base Design Blog area URL for future reference too, so you can search yourself next time:
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
Regards,
Brett