Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
@Novelchew can you share a sample data ?
can you share the excel?
which values you want to remove exactly?
and what is expected output should be
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
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:
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