topic Re: Help with Peek() and comparing 2 values in QlikView

Help with Peek() and comparing 2 values

phillipmacd — Thu, 02 Aug 2018 08:20:25 GMT

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	OK
2018-01-01	124	B	FAIL
2018-01-01	124	C	FAIL

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

Re: Help with Peek() and comparing 2 values

Anonymous — Thu, 02 Aug 2018 14:28:14 GMT

Hi Philip,

May I know how are you matching the DOC_NO?

Re: Help with Peek() and comparing 2 values

vishsaggi — Thu, 02 Aug 2018 15:14:55 GMT

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, 124, B

2018-01-01, 124, C

];

LEFT JOIN(Dummy)

LOAD RowNum,

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

Resident Dummy;

Re: Help with Peek() and comparing 2 values

phillipmacd — Fri, 03 Aug 2018 06:12:40 GMT

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'))

Re: Help with Peek() and comparing 2 values

phillipmacd — Fri, 03 Aug 2018 06:16:28 GMT

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

Re: Help with Peek() and comparing 2 values

tamilarasu — Fri, 03 Aug 2018 07:32:49 GMT

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;

Re: Help with Peek() and comparing 2 values

vishsaggi — Fri, 03 Aug 2018 09:54:25 GMT

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

Re: Help with Peek() and comparing 2 values

phillipmacd — Fri, 03 Aug 2018 10:06:42 GMT

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, 124, B

2018-01-01, 125, C

2018-01-01, 126, D

2018-01-01, 127, A

];

LEFT JOIN(Dummy)

LOAD RowNum,

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

Resident Dummy;

Re: Help with Peek() and comparing 2 values

phillipmacd — Fri, 03 Aug 2018 10:31:41 GMT

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.

Re: Help with Peek() and comparing 2 values

Anonymous — Fri, 03 Aug 2018 13:02:29 GMT

Hi Philip,

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

Re: Help with Peek() and comparing 2 values

phillipmacd — Fri, 03 Aug 2018 13:25:52 GMT

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

Re: Help with Peek() and comparing 2 values

vishsaggi — Fri, 03 Aug 2018 14:54:01 GMT

Hey Philip

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

Thanks,
V.

Re: Help with Peek() and comparing 2 values

Anonymous — Fri, 03 Aug 2018 19:00:32 GMT

Something like this

Re: Help with Peek() and comparing 2 values

phillipmacd — Mon, 06 Aug 2018 05:50:26 GMT

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

Re: Help with Peek() and comparing 2 values

phillipmacd — Mon, 06 Aug 2018 05:51:20 GMT

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

Re: Help with Peek() and comparing 2 values

BalaBhaskar_Qlik — Mon, 06 Aug 2018 11:24:36 GMT

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.

Re: Help with Peek() and comparing 2 values

phillipmacd — Mon, 06 Aug 2018 11:54:37 GMT

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'

Re: Help with Peek() and comparing 2 values

phillipmacd — Mon, 06 Aug 2018 12:00:46 GMT

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.

Re: Help with Peek() and comparing 2 values

Anonymous — Mon, 06 Aug 2018 21:53:06 GMT

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;

Re: Help with Peek() and comparing 2 values

phillipmacd — Tue, 07 Aug 2018 06:54:51 GMT

Thank you Kushal, will do so.