Using Where not Exists function to achieve this but not working as expected.

[iahmad9]

Hi Team,

last couple of days, I am working on it but not getting the expected output. could you please help me on it. Please find the sample data set below.

Position_A:
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Aviral

Postion_B:
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Irshad
PHI_L,PHI,Irshad
PHI_L,PHI,Irshad

Expected Output:
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Aviral
PHI_L,PHI,Irshad
PHI_B,PHI,Irshad

My Output coming like this which is not expected
Account, PAccount,Type
PL,P,Aviral
QL,Q,Aviral
QB,P4,Aviral
PHI_L,PHI,Irshad

Regards,

Irshad Ahmad

[marksouzacosta]

Hi @iahmad9 ,

I think you have a mistake on your Position_B table.
The first column of your last line says "PHI_L" but I think you are expecting "PHI_B".

In any case, can you please post your Load Script?

[iahmad9]

Hi @marksouzacosta ,

You are write.

I have corrected the data and placed once again.

Position_Aviral:
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Aviral

Postion_Irshad:
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Irshad
PHI_L,PHI,Irshad
PHI_B,PHI,Irshad

Expected Output:
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Aviral
PHI_L,PHI,Irshad
PHI_B,PHI,Irshad

Note:Due to security rule. I can not share the load script. sorry for that.

Regards,

Irshad Ahmad

[Digvijay_Singh]

I feel you need to provide more information, your real scenario may be more complex, which fields you need to compare inside where exists clause.

If you just need the expected output you mentioned in your post then there could be multiple ways to achieve that but not sure which one actually would work for you, one of them could be like this - 

Position_Aviral:
Load * inline [
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Aviral
];

Concatenate(Position_Aviral)

Load *
Where not match(Account&PAccount&Type,'QBP4Irshad');

Load distinct * inline [
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Irshad
PHI_L,PHI,Irshad
PHI_B,PHI,Irshad
];

[iahmad9]

Hi @Digvijay_Singh ,

Thank you for your prompt response,

In my requirement, I have to compare based on PAccount(Parent Account) some of the PAccount having more than two Account. we have to do all dynamically. below code look like you have added one fields static.

Note: we have condition like those PAccount(Parent Account) which are already available in first table. That PAccount need to exclude from second table.

Load *
Where not match(Account&PAccount&Type,'QBP4Irshad');

Could you please share some other way to achieve this. It would be great.

Regards,

Irshad Ahmad

[Digvijay_Singh · Accepted solution]

try something like this may be - 

Position_Aviral:
Load *,PAccount as PAccount1 inline [
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Aviral
];

NoConcatenate

Position_Irshad:
Load * inline [
Account, PAccount,Type
PL,P,Aviral
PB,P,Aviral
QL,Q,Aviral
QB,P4,Irshad
PHI_L,PHI,Irshad
PHI_B,PHI,Irshad
];

NoConcatenate
final:
Load *
Resident Position_Aviral;
Concatenate(final)
Load distinct *
resident Position_Irshad
Where not exists(PAccount1,PAccount);

Drop Tables Position_Aviral,Position_Irshad;

[iahmad9]

Thank you @Digvijay_Singh ;

I am getting the expected output. Will send the report to user and cross check. But as per the requirement expected output coming.

Regards,

Irshad Ahmad