Peek Function

[upaliwije]

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.

[jonathandienst]

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

[jonathandienst]

Possibly something like

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

(assuming dimensions of CRT_REF_2 and CRF)

[upaliwije]

Thanks  Jonty,

I tried with your expression and get following error

I have also attached sample data . Kindly advise me

[upaliwije]

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 

[jonathandienst · Accepted solution]

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 🙂