topic Re: Peek Function in QlikView

Peek Function

upaliwije — Thu, 10 Jan 2019 05:45:42 GMT

Hi Friends

I have following data set in my Qv Document

Data set

After running the following script I get the output shown below

LOAD
*,
IF(PEEK('CRT_REF_2')=CRT_REF_2 and peek('CRF')='A','V','NV')AS VFLG
Resident CRE
ORDER BY CRT_REF_2,CRF ASC;

Current output & expected out put

my Expected output is shown next to current output.

My requirement is If there two or more records under same Ref_no 'CRF'='B' should have V flag and 'A' should have NV flag. If there exists only one record Flag should be V irrespective of the CRF A or B

Kindly help me to modify my script to achieve my requirment.

Re: Peek Function

jonathandienst — Thu, 10 Jan 2019 06:26:37 GMT

Please post your data as text rather than an image. Or upload a small sample with some data in it.

Re: Peek Function

jonathandienst — Thu, 10 Jan 2019 06:31:11 GMT

Possibly something like

If(Count(TOTAL <CRT_REF_2> CRF) > 1 And CRF = 'A', 'NV', 'V')

(assuming dimensions of CRT_REF_2 and CRF)

Re: Peek Function

upaliwije — Thu, 10 Jan 2019 09:03:34 GMT

Thanks Jonty,

I tried with your expression and get following error

I have also attached sample data . Kindly advise me

Re: Peek Function

upaliwije — Thu, 10 Jan 2019 09:40:36 GMT

Sorry Jonty,

I got your reply wrong. What you have suggested is expression. I thought It is a script. When I use it as an Expression it works perfectly. But my requirement is to have it it in data load script since I have to do further modification in the same script. Further there are more than 2 million records and It takes lot of time for processing. Pls advise me a suitable data load script Thanks

Re: Peek Function

jonathandienst — Thu, 10 Jan 2019 09:57:49 GMT

aa:
LOAD CRT_REF_2, 
     CRF
From [D:\Qlikview\Lesson\data.xls]
(biff, embedded labels, table is Sheet1$);

Join(aa)
LOAD Count(CRT_REF_2) as Count_CRT_REF2,
	CRT_REF_2
Resident aa
Group By CRT_REF_2;

aaa:
LOAD
	*,
	If(Count_CRT_REF2 > 1 And CRF = 'A', 'NV', 'V') as VFLG
Resident aa;

DROP Table aa;

You might want to use more meaningful table names 🙂