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....

[phillipmacd]

Hi Kushal, attached is an example of my data, i know its only a few records ( current data is about 6 million records)

so its difficult to check by hand.

the sample data shows how the transactions work, for each DOC_NO there are 2 or more records for both debit and credit transactions( depending on the amount of debits or credits). These should always sum to 0(zero) on the CUSTOMER 's account. I need to test weather the transactions for debit and credit are only on 1(one) CUSTOMER per DOC_NO.

So I need to match DOC_NO in records and when a match is found I need to test the CUSTOMER and verify that it is the same CUSTOMER for both the debit and credit for the DOC_NO.

Hope this help explain the data

[vishsaggi]

Hey Philip

DId you try what Tamil suggested? If not try that it will solve your issue.

Thanks,
V.

[Anonymous]

Something like this

[phillipmacd]

Hi V, I am busy testing the result, just need to check if all is ok.

[phillipmacd]

Hi Kushal, that is exactly what i am looking to do

[balabhaskarqlik]

May be this:

If(DOC_NO = Peek(DOC_NO) and CUSTOMER = Peek(CUSTOMER),'OK','FAIL') as Flag;

Checking the flag on both doc_no and customer details.

[phillipmacd]

Hi Bala

NO success. flags the one side of the DOC_NO (debit or credit) as 'OK' and the other (credit or debit) as 'FAIL'

[phillipmacd]

Hi All, at this stage i am stuck, i am going to rather check when the amount (both debit and credit) of the DOC_NO does not sum to zero(0).

This should give  a better view on the different CUSTOMERS linked to a DOC_NO.

Thank you all for the input.

[Anonymous · Accepted solution]

Let me know if this works for you.

Temp:

LOAD DOC_NO,

     DATE,

     CUSTOMER,

     AMOUNT,

     DOC_NO & CUSTOMER as UJt,

     DOC_NO & CUSTOMER as UJ

FROM

(ooxml, embedded labels, table is Sheet3);

left join(Temp)

LOAD UJt as UJ,

if(SUM(AMOUNT)<>0,'Fail','Ok') as Flag

Resident Temp group by UJt;

[phillipmacd]

Thank you Kushal, will do so.