topic Re: Complex Cross tab file processing in QlikView

Complex Cross tab file processing

Sean_BI — Sat, 16 Nov 2024 19:22:47 GMT

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.

Re: Complex Cross tab file processing

lorenzoconforti — Sun, 29 Dec 2019 01:08:34 GMT

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

Re: Complex Cross tab file processing

Sean_BI — Sun, 29 Dec 2019 12:25:41 GMT

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.

Re: Complex Cross tab file processing

lorenzoconforti — Sun, 29 Dec 2019 12:48:38 GMT

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

Re: Complex Cross tab file processing

Brett_Bleess — Mon, 30 Dec 2019 14:12:32 GMT

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