topic Re: SUM IF WITH CONDITION in QlikView

SUM IF WITH CONDITION

entsh — Tue, 13 Jul 2021 14:15:07 GMT

PLEASE SEE THE TABLES BELOW:

TBL1:

name	ST	FN	plan
A	500	700	10000
B	500	600	8000
C	700	900	6000

TBL2:..

NAME	NUMBER	TED
A	500	4000
A	550	3000
A	800	3000
B	550	9000
B	600	1000
B	650	1000
C	600	1000
C	650	2000

IF SUM OF "TED" FOR EACH "NAME" (JUST FOR NUMBERS BETWEEN ST AND FN) IS LARGER THAN PLAN SHOW "OK" ELSE SHOW "NOK"

RESULT

NAME	STATUS
A	NOK (SUM TED FOR NUMBERS BETWEEN ST AND FN=4000+3000=7000<10000
B	OK (SUM TED FOR NUMBERS BETWEEN ST AND FN=9000+1000=10000>8000
C	NOK (SUM TED FOR NUMBERS BETWEEN ST AND FN=0<6000

Re: SUM IF WITH CONDITION

MayilVahanan — Tue, 13 Jul 2021 15:03:05 GMT

HI Entsh

Try with intervalmatch concept for this scenario.

Something like below

T1:
LOAD * INLINE [
NAME, ST, FN, Plan
A, 500, 700, 10000
B, 500, 600, 8000
C, 700, 900, 6000
];

Plan:
Load NAME, Plan Resident T1;

DROP Field Plan From T1;

T2:
LOAD * INLINE [
NAME, NUMBER, TED
A, 500, 4000
A, 550, 3000
A, 800, 3000
B, 550, 9000
B, 600, 1000
B, 650, 1000
C, 600, 1000
C, 650, 2000
];

IntervalMatch(NUMBER, NAME)
LOAD ST, FN, NAME Resident T1;

Join
Load * Resident T1;

Join
Load * Resident T2;

DROP Table T1, T2;

O/p:

Re: SUM IF WITH CONDITION

Saravanan_Desingh — Wed, 14 Jul 2021 02:23:28 GMT

One more solution,

tab1: LOAD * INLINE [ NAME, ST, FN, Plan A, 500, 700, 10000 B, 500, 600, 8000 C, 700, 900, 6000 ]; Left Join(tab1) tab2: LOAD * INLINE [ NAME, NUMBER, TED A, 500, 4000 A, 550, 3000 A, 800, 3000 B, 550, 9000 B, 600, 1000 B, 650, 1000 C, 600, 1000 C, 650, 2000 ]; Left Join(tab1) LOAD NAME, If(Sum(If(NUMBER>=ST And NUMBER<=FN,TED))>=Only(Plan),'OK','NOK') As Status Resident tab1 Group By NAME;

Re: SUM IF WITH CONDITION

Saravanan_Desingh — Wed, 14 Jul 2021 02:24:04 GMT

Output: