Complex Cross tab file processing

[Sean_BI]

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.

[lorenzoconforti · Accepted solution]

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

[Sean_BI]

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.

[lorenzoconforti · Accepted solution]

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

[Brett_Bleess]

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