Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends
I have following data set in my Qv Document
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;
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.
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 🙂
Please post your data as text rather than an image. Or upload a small sample with some data in it.
Possibly something like
If(Count(TOTAL <CRT_REF_2> CRF) > 1 And CRF = 'A', 'NV', 'V')
(assuming dimensions of CRT_REF_2 and CRF)
Thanks Jonty,
I tried with your expression and get following error
I have also attached sample data . Kindly advise me
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
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 🙂