Where not exist issue

[Novelchew]

Need help with where not exist issue.

My SR from TableA is not getting removed because in the data itself there are spaces example "SR123    ", which QV couldn't read it and remove them.

I'm able to remove those spaces with below but where not exist doesn't work due to the spaces
KeepChar([SR], ' qazwsxedcrfvtgbyhnujmikolpABCDEFGHIJKLMNWOPQRSTUVXYZ1234567890-') AS [SR],

Any idea how to resolve this?

TableA:
Load [SR] as [SR A]

From

Downloads.......xxxx.xlsx;

TableB:

Load [SR No] as [SR No]

From
Downloads......DDDD.xlsx;
Where not exists([SR],[SR No]);

DropTableA:

[Taoufiq_Zarra]

@Novelchew  can you share a sample data ?

[Chanty4u]

can you share the excel?

which values you want to remove exactly?

and what is expected output should be

[marcus_sommer · Accepted solution]

If you rename SR you need to refer to the new fieldname like:

... Where not exists([SR_A],[SR No]);

Further if both of your files contain these undefined chars you may skip your keepchar() approach or you need to apply it on both sides like:

... Where not exists([SR_A],KeepChar([SR No], ' qazwsxedcrfvtgbyhnujmikolpABCDEFGHIJKLMNWOPQRSTUVXYZ1234567890-'));

Beside this exists() refers to fieldvalues of a field not only to fieldvalues within a table - means any other loads before or in-between your mentioned loads which contain these fields (origin or renamed) will impact the results.

- Marcus 

[QFabian]

hi, try to use trim function , example : 

TableA:
Load [SR] as [SR A]

From

Downloads.......xxxx.xlsx;

TableB:

Load trim([SR No]) as [SR No]

From
Downloads......DDDD.xlsx;
Where not exists([SR A],trim([SR No]));

DropTableA:

[Novelchew]

Hi all!

Thanks for the help! 
Managed to resolve this with Marcus's help!

If you rename SR you need to refer to the new fieldname like:

... Where not exists([SR_A],[SR No]);

Further if both of your files contain these undefined chars you may skip your keepchar() approach or you need to apply it on both sides like:

... Where not exists([SR_A],KeepChar([SR No], ' qazwsxedcrfvtgbyhnujmikolpABCDEFGHIJKLMNWOPQRSTUVXYZ1234567890-'));

Beside this exists() refers to fieldvalues of a field not only to fieldvalues within a table - means any other loads before or in-between your mentioned loads which contain these fields (origin or renamed) will impact the results.

- Marcus