topic Re: Conditional Column's calculation in QlikView

Conditional Column's calculation

Anonymous — Tue, 04 Nov 2014 22:24:00 GMT

Hi All

I have data like

LineNo LineName Sales

1 A 10

2 B 20

3 C 30

4 D 40

5 E 50

And requirements is like below

Head LineNos Sales

Sales A 1 10

Sales B 2+4-3 30

Sales C 3+4+5-1 110

Sales D 2 20

Sales E 3 30

Sales F 4 40

....

and 200 lines like these where Sales are based on LineNos condition(Sum or subtraction only)

How do I create variable where IF special instruction (like for Sales B & Sales C) fulfilled ELSE can also fulfilled

Thanks in advance

Re: Conditional Column's calculation

JonnyPoole — Tue, 04 Nov 2014 22:55:02 GMT

A not bad way of managing this is to create a 'component multipliers' table that maps each LineNo used to calculate head Sales Head. Also include a multiplier (-1 or +1) to figure out if that LineNo in that Head is adding or subtracting from the total.

Then the expression in the chart is sum(Sales * Multiplier) , and you can get these results easily in straight / pivot table

LOAD LineNo,

LineName,

Sales

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

ComponentMultipliers:

LOAD * INLINE [

Head, LineNo, Multiplier

A, 1, 1

B, 2, 1

B, 4, 1

B, 3, -1

C, 3, 1

C, 4, 1

C, 5, 1

C, 1, -1

D, 2, 1

E, 3, 1

F, 4, 1

];

Re: Conditional Column's calculation

Anonymous — Wed, 05 Nov 2014 01:22:47 GMT

Hi Jonathan

I appreciate your help by providing very helpful answer.

Thanks lot

Re: Conditional Column's calculation

JonnyPoole — Wed, 05 Nov 2014 01:46:54 GMT

Sathish - thank you. Kindly mark any posts you find helpful by designating them 'helpful'

Re: Conditional Column's calculation

Anonymous — Wed, 05 Nov 2014 02:52:16 GMT

I do not see those buttons

Re: Conditional Column's calculation

JonnyPoole — Wed, 05 Nov 2014 03:09:36 GMT

Here is a link that may help with that

QlikCommunity Tip: How to get answers to your post?