Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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).