Offset Flag

[shayd]

Hi all,

I'm trying to create a schema based flag that represents that an offset was made over an invoice.
For Example:

First_ID	Second_ID	Sum	Flag
330028574	210509309	-142	1
330028575	210509411	456	1
330028580	210509727	-141	1
330028576	210509214	350	1
330028577	210509679	324	1
330028580	210509727	282	1
330028614	210516854	-420.93	0
330028614	210516854	420.93	0
330028577	210509679	-162	1
330028576	210509214	-175	1
330028589	210525919	1303.13	1
330028606	210516376	1197	1
330028606	210516376	-399	1
330028617	210516626	280	1
330028575	210509411	-228	1
330028589	210525919	-3127.51	0
330028589	210525919	3127.51	0

Since 330028589 &  210525919 showed twice, once without minus sign and once with minus sign for the same amount,
I can tell that an offset was made and therefore I would like to put a flag of zero near them for example.

I've tried to use some peek and fabs function to get that:

T1:
LOAD * Inline
[
First_ID, Second_ID, Sum
330028574, 210509309, -142.00
330028575, 210509411, 456.00
330028589, 210525919, -3127.51
330028580, 210509727, -141.00
330028576, 210509214, 350.00
330028577, 210509679, 324.00
330028580, 210509727, 282.00
330028614, 210516854, -420.93
330028589, 210525919, 3127.51
330028577, 210509679, -162.00
330028576, 210509214, -175.00
330028589, 210525919, 1303.13
330028606, 210516376, 1197.00
330028606, 210516376, -399.00
330028614, 210516854, 420.93
330028617, 210516626, 280.00
330028575, 210509411, -228.00
];

NoConcatenate

T2:
LOAD *,
fAbs(Sum) as fabs
Resident T1;

T3:

LOAD

*,

IF(First_ID=Peek('First_ID') AND Second_ID=Peek('Second_ID') AND fabs=peek('fabs'), 0, 1) AS Flag_Is_Not_Offsetting

RESIDENT T2

ORDER BY First_ID, Second_ID, fabs ASC;
DROP Tables T1,T2;

But it's not good enough, the second value doesn't get the flag and I don't know how to deal with ordering the keys in such a way that a random value with  the concurrent keys won't disturb.

Thanks.

[chrismarlow · Accepted solution]

Hi,

Maybe just left join T1 back to itself negating the Sum? So after your inline load;

Left Join (T1)
LOAD
	First_ID, Second_ID, -Sum AS Sum, 'Y' as matched
Resident T1;

final:
NoConcatenate
Load	 
	First_ID, Second_ID, Sum,
	If(matched='Y',0,1) AS Flag
Resident T1;

DROP Table T1;

Cheers,

Chris.

[shayd]

Excellent idea, Thank you chrismarlow, that worked! 👍