Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
manpri7078
Creator
Creator

Showing Error in Pivot Table

Hi All.

I am making a pivot table to analyze some Data. Here is screen shot for one particular date :

Date Ship Customer Name Quantity Price Revenue Error Status
20-Jun-17 P C MALPANI & COMPANY-STOCKIST 0.594154.100915351
B R INDUSTRIES LIMITED-STOCKIST 2.2441
SHRI TAPODHANI ALUMINIUM TRADING COMPANY-STOCKIST 1.069155.1001658021
PVC DECORATORS-STOCKIST 2.264154.1003488821

In this Pivot Table Row No 2 representing Customer name as BR Industries Limited-Stockist is showing Price as blank because 2.244 is the sum total of one or more quantity but the price for the same is different which should be same as per my company's policy. I want to track where price is different so that price column be not populated and hence I can concentrate on those types of line items.

Now I want the last column to show 1 or 0 depending upon the situation whether price is blank or contains some figure.

Secondly I want to filter the last column to select either 0 or 1 to have a glance on the desired situation.

I am trying to define expression in the last column in the form of if(column(3)='',0,1), but it is not giving me desired output as can be seen from the table.

Please advise me how to build such Pivot Table as I have not made Pivot Table before and I am absolutely new in this area.

Regards,

Manish Prasad

27 Replies
sunny_talwar

Not entirely sure what sorting requirement do we have here?

vishsaggi
Champion III
Champion III

In the attached document there are some 0's in the status column which is a calculated expression. But when we use this in our sort expression its not sorting. He wants all 0's to be on top and 1's after.

sunny_talwar

The only way I was able to get this done is by adding another dimension to the table (called Key)

Capture.PNG

We can hide Key using Pivot Column Width to Zero

Key is created like this in the script

Table1:

LOAD AutoNumber([Excise Invoice Date]&'|'&[Alloy Temper]&'|'&[Composition]&'|'&[Ship Customer Name]) as Key,

[Alloy Temper],

     [Bill Customer Name],

     [Bill Customer No],

     Composition,

     [Cst Amt],

     [Customer Class Code],

Basically took all the dimensions from the chart to create a unique value for each row.

View solution in original post

vishsaggi
Champion III
Champion III

Thanks Sunny, lets see if that works for Manish.

manpri7078
Creator
Creator
Author

Dear Sunny,

Once again thanks for your suggestion. You are able to do in Pivot Table. Bravo.

But can the display presentation be like usually we have for Pivot Table. The Table is looking like straight table but I want the display like Pivot Table. If the Key dimension is not there then it is looking like Pivot though.

If possible then please guide me otherwise It is OK. The same presentation I was able to do in straight table as in straight table sorting can be done on expressions easily.

I was trying to achieve the same result in the form of Pivot Table Presentation.

If possible then please guide me.

Waiting for your reply.

If not possible then I will mark it correct after getting your reply.

Regards

Manish Prasad

manpri7078
Creator
Creator
Author

It does works for me but the table is looking like straight table and not the way I was trying to make. But anyway thanks for helping me. I was able to achieve the same though Straight Table, but I was trying to do the same through Pivot Table concept and hence was my query.

Regards,

Manish Prasad

sunny_talwar

Sorting has been always an issue within pivot table.... the problem is that how do you imagine to see all the 0's at the top while still maintaining the groupings. For example... lets look at this

Capture.PNG

How do you imagine to see 10-Dec-16 and 12-May-17 at the if there are more entries for 10-Dec-16.... even within 10-Dec-16, you have two different Alloy Tempers.... so, I guess either you can bring all the 0's to the top or you can maintain all the groupings (the pivot table view).

Does that make sense? or am I missing what the final output you are looking to get?

manpri7078
Creator
Creator
Author

Dear Sunny,

I fully agree with you. What I want can not be done through Pivot Table view. So I have decided to do it through straight table itself which is the right one for my output.

But anyway thanks for your support and guidance and the way you managed to do the same in a pivot table is outstanding.

Your guidance has as usual always helped me.

Regards,

Manish Prasad