Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
skompel2
Contributor II
Contributor II

Conditional Sum on Conditional Dimension

I have this table:

CodePart DescLocationLot SizeComplete Qty
Bar101 (TD only)110
Bar102X15
Bar101 (TD ONLY)210
StickA10X11
StickA11 (TD ONLY)210
StickA11 (TD ONLY)111
Bar102Y12
StickA10Z13

 

I want to make pivot table with calculated dimension such that locations 1,2 are grouped as NEW and locations X,Y,Z are OLD. Then, for parts in NEW  I want to sum the "Lot Size" column and parts in OLD to sum the "Complete Qty" column. Like this:

 PartStatusSum
Bar101 (TD ONLY)NEW2
Bar102OLD7
StickA11 (TD ONLY)NEW2
StickA10OLD4

 

My dimension is this:     =If(Match([Location],'X','Y','Z')>0,'OLD', If(Match([Location],'1','2') > 0,'NEW')) and my expression is this:   =If(Match([Location],'X','Y','Z')>0,Sum([Complete Qty]), If(Match([Location],'1','2') > 0,SUM([Lot Size])).

But it doesn't work gives Null value for the Code "STICK" etc. How to achieve this? Please Help.

2 Solutions

Accepted Solutions
sunny_talwar

Try this script

Table:
LOAD *,
	  If(Match([Location],'X','Y','Z'),'OLD',
	  	If(Match([Location], '1', '2'), 'NEW')) as Status;
LOAD * INLINE [
    Code, Part Desc, Location, Lot Size, Complete Qty
    Bar, 101 (TD ONLY), 1, 1, 0
    Bar, 102, X, 1, 5
    Bar, 101 (TD ONLY), 2, 1, 0
    Stick, A10, X, 1, 1
    Stick, A11 (TD ONLY), 2, 1, 0
    Stick, A11 (TD ONLY), 1, 1, 1
    Bar, 102, Y, 1, 2
    Stick, A10, Z, 1, 3
];

and then

Dimensions

Code
Part Desc
Status

Expression

=Pick(Match(Status, 'OLD', 'NEW'), Sum([Complete Qty]), Sum([Lot Size]))

image.png

View solution in original post

sunny_talwar

Something like this

LOAD [Code],
[Location],
If(Match([Location],'X','Y','Z'),'OLD', If(Match([Location], '1', '2'), 'NEW')) as Status,
.....etc etc
FROM [C:\Users\a316109\Desktop\new.xlsx] (ooxml, embedded labels);

View solution in original post

9 Replies
trdandamudi
Master II
Master II

May be you can do this in the script and it will be much easier like below:

Field1,

Field2,

If(Match([Location],'X','Y','Z')>0,'OLD',
   If(Match([Location],'1','2') > 0,'NEW')) as Status

Now use the "Status" field in your chart along with the other dimensions. Hope this helps...

skompel2
Contributor II
Contributor II
Author

what should be in my expression to compute different sum?

trdandamudi
Master II
Master II

Below is the full code:


Data:
Load *,
If(Match([Location],'X','Y','Z')>0,'OLD',
If(Match([Location],'1','2') > 0,'NEW')) as Status;
Load * Inline [
Code, Part Desc, Location, Lot Size, Complete Qty
Bar, 101 (TD ONLY), 1, 1, 0
Bar, 102, X, 1, 5
Bar, 101 (TD ONLY), 2, 1, 0
Stick, A10, X, 1, 1
Stick, A11 (TD ONLY),2, 1, 0
Stick, A11 (TD ONLY),1, 1, 1
Bar, 102, Y, 1, 2
Stick, A10, Z, 1, 3
]
;

 Chart Dimensions:

Code,

Part Desc

Status

Expression:

if(Status='OLD',Sum([Complete Qty]),
If(Status='NEW',SUM([Lot Size])))

 

 

sunny_talwar

Try this script

Table:
LOAD *,
	  If(Match([Location],'X','Y','Z'),'OLD',
	  	If(Match([Location], '1', '2'), 'NEW')) as Status;
LOAD * INLINE [
    Code, Part Desc, Location, Lot Size, Complete Qty
    Bar, 101 (TD ONLY), 1, 1, 0
    Bar, 102, X, 1, 5
    Bar, 101 (TD ONLY), 2, 1, 0
    Stick, A10, X, 1, 1
    Stick, A11 (TD ONLY), 2, 1, 0
    Stick, A11 (TD ONLY), 1, 1, 1
    Bar, 102, Y, 1, 2
    Stick, A10, Z, 1, 3
];

and then

Dimensions

Code
Part Desc
Status

Expression

=Pick(Match(Status, 'OLD', 'NEW'), Sum([Complete Qty]), Sum([Lot Size]))

image.png

View solution in original post

skompel2
Contributor II
Contributor II
Author

Hi, I understood the script functionality. But I'm using this pivot table in a dashboard( by "create new sheet object") and my datasource is an excel file. The script editor already has all columns loaded in like this:

LOAD [Code],[Location],.....etc etc

FROM [C:\Users\a316109\Desktop\new.xlsx] (ooxml, embedded labels);

How can I add your script to the above preloaded script to get my pivot table?

sunny_talwar

Something like this

LOAD [Code],
[Location],
If(Match([Location],'X','Y','Z'),'OLD', If(Match([Location], '1', '2'), 'NEW')) as Status,
.....etc etc
FROM [C:\Users\a316109\Desktop\new.xlsx] (ooxml, embedded labels);

View solution in original post

skompel2
Contributor II
Contributor II
Author

Thank you Sunny and Trdandamudi! Both your solutions work . But I found that we needn't use script editor, just use the "Add Calculated Dimensions" and put the label as "Status". Then use any of the Sum formula given above in "Expressions" otherwise it'll show Null value.
You guys are doing a wonderful work. 🙂

sunny_talwar


@skompel2 wrote:

Thank you Sunny and Trdandamudi! Both your solutions work . But I found that we needn't use script editor, just use the "Add Calculated Dimensions" and put the label as "Status". Then use any of the Sum formula given above in "Expressions" otherwise it'll show Null value.
You guys are doing a wonderful work. 🙂


You are right, you don't have to use Script to create a new field, but it is generally advisable to create a new field instead of using a calculated dimensions which are usually less efficient compared to a field when used as a dimension

vistab3st
Contributor
Contributor

I have an other issue some how similar,I have two sheets in one file with staff working hours.I need to combine both tables and figure each person's total working hour , and as weekly normal working hours is 40H extract how much they have overtime .

Full name (first and last name)Monday 1st JanTuesday 2nd JanWednesday 3rd JanThursday 4th JanFriday 5th JanSaturday 6th JanSunday 7th Jan
John Smith9:30:009:00:009:00:009:00:008:30:00  
Lonna Morphy7:20:009:30:0010:15:009:00:0010:20:000:00:000:00:00
Philip Fready10:30:009:00:0010:00:009:00:009:30:000:00:000:00:00
Mina Malik10:30:009:00:0012:00:009:00:009:30:000:00:000:00:00
Deniz Donmez8:30:009:00:008:30:009:00:009:00:000:00:000:00:00
Gupta Kumar9:15:0010:00:009:30:009:15:009:00:000:00:000:00:00

 

Full name (first and last name)Monday 8th JanTuesday 9th JanWednesday 10th JanThursday 11th JanFriday 12th JanSaturday 13th JanSunday 14th Jan
John Smith8:00:008:00:008:00:008:00:008:00:000:00:000:00:00
Lonna Morphy10:00:0010:30:0010:00:0011:00:005:00:000:00:000:00:00
Philip Fready10:00:0010:00:008:00:008:00:008:00:000:00:000:00:00
Mina Malik8:00:008:00:008:00:008:00:008:00:000:00:000:00:00