topic Re: Where not exist issue in QlikView

Where not exist issue

Novelchew — Wed, 25 Nov 2020 16:16:04 GMT

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:

Re: Where not exist issue

Taoufiq_Zarra — Tue, 13 Oct 2020 07:52:38 GMT

@Novelchew can you share a sample data ?

Re: Where not exist issue

Chanty4u — Tue, 13 Oct 2020 08:16:35 GMT

can you share the excel?

which values you want to remove exactly?

and what is expected output should be

Re: Where not exist issue

marcus_sommer — Tue, 13 Oct 2020 13:27:00 GMT

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

Re: Where not exist issue

QFabian — Tue, 13 Oct 2020 13:36:49 GMT

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:

Re: Where not exist issue

Novelchew — Thu, 15 Oct 2020 07:00:11 GMT

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-'));

- Marcus