topic Re: Keep issue : putting conditional statements while doing keep in QlikView

Keep issue : putting conditional statements while doing keep

fallenangel6 — Wed, 25 Nov 2020 16:16:04 GMT

hi all,

i have two tables base table and validation table. base table have a counter .when i do left join between base and validation, i am getting extra duplicate records and so when i take sum(counter), the count is more . i tried using left keep and count now matches the desired count but the problem is i have done some if conditions and all like below in validation table

If(IsNull(Email_validated_manually) or Email_validated_manually='',0,Email_validated_manually) as Email_Validated_Final,// 1 or 0 values to be used as flags

If(Email_validated_manually=1,'Yes','No') as Email_Validated,// for filters in front end

and this maps incorrectly when left keep is done.

Please help.Sample data given below.

i cant put up an accurate scenario here but in my data model, by using left keep my count matches. but i need to create some field based on the validation table to filter the base table. how can that be possible?

base table:

clsid	partyrk	email correct pattern	email incorrect pattern	Pan correct pattern	Pan incorrect pattern
1111	1	1	0	1	0
1111	2	0	0	0	1
1111	3	1	0	1	1
1111	4	0	1	0	1
2222	5	1	0	1	0
2222	6	0	1	1	0
2222	7	1	1	0	0
3333	8	0	1	1	0

validation table:

partyrk	Email validated manually	Email Manual comment	pan validated manually	Pan Manual comment
4	1	correct Email
8	1	correct Email pattern
4	1	email id correct	1	Correct Pan

the desired output isgiven below.

Please help asap.

clsid	partyrk	email correct pattern	email incorrect pattern	Pan correct pattern	Pan incorrect pattern	Email validated manually	Email Manual comment	pan validated manually	Pan Manual comment	Email Validated	pan validated
1111	1	1	0	1	0					No	No
1111	2	0	0	0	1					No	No
1111	3	1	0	1	1					No	No
1111	4	0	1	0	1	1	email id correct	1	Correct Pan	Yes	Yes
2222	5	1	0	1	0					No	No
2222	6	0	1	1	0					No	No
2222	7	1	1	0	0					No	No
3333	8	0	1	1	0	1	correct Email pattern			Yes	No

Re: Keep issue : putting conditional statements while doing keep

MindaugasBacius — Thu, 28 Feb 2019 14:37:57 GMT

How do you know which row to connect from Validation table for example when partyrk = 4?

Re: Keep issue : putting conditional statements while doing keep

Sue_Macaluso — Thu, 28 Feb 2019 20:22:31 GMT

Is this for QlikView or Sense? I would like to move this to the correct product forum

Re: Keep issue : putting conditional statements while doing keep

fallenangel6 — Fri, 01 Mar 2019 03:12:46 GMT

I tried using updated date for each record( that is another field in
validation table) but there are multiple records for the same partyrk
updated on the same day.i used max(updated day) to fetch most recent record
but bcz of this duplication,im having troubles.

Re: Keep issue : putting conditional statements while doing keep

fallenangel6 — Fri, 08 Mar 2019 11:47:17 GMT

best record. i took max (email validated manually) for the id but had trouble getting the text one.

Re: Keep issue : putting conditional statements while doing keep

fallenangel6 — Fri, 08 Mar 2019 11:47:30 GMT

qlikview