Help with Peek() and comparing 2 values

[phillipmacd]

Hi all.

Hope someone could direct me in the right direction on solving my problem.

I need to flag records in a table on matching criteria. I think the best way is going to have to use the peek() function.

I have a few dimension containing transaction data. I need to check for matching doc numbers and where they are found check the customer linked to the document.

example of data:

DATE	DOC_NO	CUSTOMER	#FLAG
2018-01-01	123	A	OK
2018-01-01	123	A
2018-01-01	124	B	FAIL
2018-01-01	124	C

if the document numbers match and the customer is a match for that document number it should be flagged as "OK", but where

the document numbers match and the customer differs it needs to be flagged as "FAIL"

I can find the matching DOC_NO, but i have no idea how to check the CUSTOMER when it is found, any ideas??

Thank you in advance....

[Anonymous]

Hi Philip,

May I know how are you matching the DOC_NO?

[vishsaggi]

May be try this?

Dummy:

LOAD *, RowNo() AS RowNum, DOC_NO&CUSTOMER AS MergeField INLINE [

DATE, DOC_NO, CUSTOMER

2018-01-01, 123, A

2018-01-01, 123, A

2018-01-01, 124, B

2018-01-01, 124, C

];

LEFT JOIN(Dummy)

LOAD RowNum,

     IF(MergeField = Peek('MergeField',0,'Dummy'), 'OK','FAIL') AS Flag

Resident Dummy;

[phillipmacd]

Hi, i just use ....

If(DOC_NO = Peek(DOC_NO),......

the main problem I have at this stage is that the match on CUSTOMER must only happen when the DOC_NO match is found.Then i tried something but i didn't get the result I needed ...

If(DOC_NO = Peek(DOC_NO),If(CUSTOMER = Peek(CUSTOMER),'OK','FAIL'))

[phillipmacd]

Hi Vishwarath, thank you for the reply.This seem to work but it only flagged the 1st DOC_NO and CUSTOMER and flagged the rest as FAIL. Not sure is stepping trough all the RowNum values

[tamilarasu]

Hi Philipp,

Try this,

Data:
LOAD Recno() as RowNo,
     DOC_NO & CUSTOMER as TempKey,
     DATE, 
     DOC_NO, 
     CUSTOMER
FROM
[https://community.qlik.com/thread/309835]
(html, codepage is 1252, embedded labels, table is @1); 

Left Join
Load TempKey,
If(Count(TempKey)>1,'OK','FAIL') as #FLAG
Resident Data Group by TempKey; 

DROP Field TempKey;

[vishsaggi]

‌it should can you share what you trying in your expr?

[phillipmacd]

o, sorry . I just noticed that I had to explain more.

It worked on the data as is( in this post), when i changed the data so that all flags should return "OK", only the fist set

was flagged "OK" and the rest where "FAIL"

Dummy:

LOAD *, RowNo() AS RowNum, DOC_NO&CUSTOMER AS MergeField INLINE [

DATE, DOC_NO, CUSTOMER

2018-01-01, 123, A

2018-01-01, 123, A

2018-01-01, 124, B

2018-01-01, 124, B

2018-01-01, 125, C

2018-01-01, 125, C

2018-01-01, 126, D

2018-01-01, 126, D

2018-01-01, 127, A

2018-01-01, 127, A

];

LEFT JOIN(Dummy)

LOAD RowNum,

     IF(MergeField = Peek('MergeField',0,'Dummy'), 'OK','FAIL') AS Flag

Resident Dummy;

[phillipmacd]

I will try and expand a bit more on the data.

     the data is transactions for a customer between accounts of the same customer.

     So every DOC_NO must have 2 (or more depending on debits and credits) records, one for debit and one for credit. and the sum on the amount for the DOC_NO should always be 0(zero)[debit and credit balance]

the test i am looking to do is to make sure that a DOC_NO has a debit and credit for one CUSTOMER.

if that is not the case the debit or credit of that DOC_NO is to incorrect CUSTOMER

hope this help to understand what i am looking to do.

[Anonymous]

Hi Philip,

Can you post some sample data so that we can have a good understanding of it,