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 |
