Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Sean_BI
Creator
Creator

Complex Cross tab file processing

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.

 

 

 

Labels (1)
2 Solutions

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

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

 

Transpose.png

 

 

View solution in original post

lorenzoconforti
Specialist II
Specialist II

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

View solution in original post

4 Replies
lorenzoconforti
Specialist II
Specialist II

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

 

Transpose.png

 

 

View solution in original post

Sean_BI
Creator
Creator
Author

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
Specialist II
Specialist II

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

View solution in original post

Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.