Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
Excellent idea, Thank you chrismarlow, that worked! 👍