SUM IF WITH CONDITION

[entsh]

HI

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

[MayilVahanan · Accepted solution]

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:

[Saravanan_Desingh · Accepted solution]

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;

[Saravanan_Desingh]

Output: