Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
iahmad9
Partner - Contributor II
Partner - Contributor II

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

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

Labels (1)
1 Solution

Accepted Solutions
Digvijay_Singh

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;

Digvijay_Singh_0-1709096071830.png

 

View solution in original post

6 Replies
marksouzacosta
Partner - Creator II
Partner - Creator II

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?

Read more at Data Voyagers - datavoyagers.net
iahmad9
Partner - Contributor II
Partner - Contributor II
Author

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
];

Digvijay_Singh_0-1709092030572.png

 

 

 

iahmad9
Partner - Contributor II
Partner - Contributor II
Author

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

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;

Digvijay_Singh_0-1709096071830.png

 

iahmad9
Partner - Contributor II
Partner - Contributor II
Author

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