left Keep issue

[fallenangel6]

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 is

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

[wandererroch]

Unless you can get your validation table down to a single row per partyrk you are going to have trouble with this.  Can you summarize the validation table into a single (most recent) row per partyrk?  Using that should give you the result you seek.  other than that you'll need to get fancy parsing the table with if statements (not fun).

[fallenangel6]

I tried using updated date(which is another column in validation table) to
fetch the most recent column but the problem I face now is that same
partyrk is updated more than once on the max updated date.. what a mess..