Qlik Community

Qlik Support Discussions

Discussion Board for collaboration related to Qlik Support.

Highlighted
upaliwije
Contributor II

Peek Function

Hi Friends

I have following data set in my Qv Document

Screenshot_1.pngData 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;

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

 

1 Solution

Accepted Solutions
MVP
MVP

Re: Peek Function

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 Smiley Happy

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
5 Replies
MVP
MVP

Re: Peek Function

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Peek Function

Possibly something like

 

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

(assuming dimensions of CRT_REF_2 and CRF)

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
upaliwije
Contributor II

Re: Peek Function

Thanks  Jonty,

 

I tried with your expression and get following error

Screenshot_1.png

I have also attached sample data . Kindly advise me

upaliwije
Contributor II

Re: Peek Function

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 

 

MVP
MVP

Re: Peek Function

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 Smiley Happy

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser