Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
shayd
Contributor III
Contributor III

Offset Flag

Hi all,

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

First_IDSecond_IDSumFlag
330028574210509309-1421
3300285752105094114561
330028580210509727-1411
3300285762105092143501
3300285772105096793241
3300285802105097272821
330028614210516854-420.930
330028614210516854420.930
330028577210509679-1621
330028576210509214-1751
3300285892105259191303.131
33002860621051637611971
330028606210516376-3991
3300286172105166262801
330028575210509411-2281
330028589210525919-3127.510
3300285892105259193127.510


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.

Untitled.jpg

Thanks.


1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

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.

View solution in original post

2 Replies
chrismarlow
Specialist II
Specialist II

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
Contributor III
Contributor III
Author

Excellent idea, Thank you chrismarlow, that worked! 👍