topic Re: exclude matching data in QlikView

exclude matching data

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

Hello...have been lurking for quite a while and getting many answers. What a great community! Thank you all for all your efforts.

Today I have a problem I cannot find a solution for. Seems simple enough though.

I wish to create a button that sets filters in place that shows me all Purchase Orders made yesterday where the Currency does not match the HomeCurrency. I have the selection for Order_Date =Today()-1 to get yesterdays PO's but cannot figure out which properties to select so it will pick rows of data where these 2 fields do not match. We place the PO in Euro's but our HomeCurrency is USD. I wish to exclude any where they do match...USD to USD or Euro to Euro.

Select all where they don't match or exclude all that do match?

Maybe there is an easier way than a button? This will be standard report though and ran daily.

Any help is appreciated!

Chuck

Re: exclude matching data

isingh30 — Tue, 22 May 2018 16:29:02 GMT

Please share sample application or data in excel sheet?

Thank you!

Re: exclude matching data

Anonymous — Tue, 22 May 2018 16:55:36 GMT

Thank you...here is sample set. This is a global tool so Home Currency can be any...and PO's go across the globe as well. My criteria will be yesterdays PO's over a certain $ limit and where currency does not match home currency. This is to identify orders that should be currency hedged.

PO#	Order_Date	PO_Currency	HomeCurrency
414751	21.5.2018	USD	USD
303328	21.5.2018	EUR	EUR
303328	21.5.2018	EUR	USD
303699	21.5.2018	CAD	USD
304196	21.5.2018	USD	USD
304306	21.5.2018	USD	RMB
304349	21.5.2018	USD	USD
304484	21.5.2018	CAD	CAD
304580	21.5.2018	USD	USD
304580	21.5.2018	USD	USD
414740	21.5.2018	EUR	RMB
414740	21.5.2018	USD	EUR
414749	21.5.2018	USD	USD
305632	21.5.2018	EUR	EUR
414743	21.5.2018	USD	USD
415024	21.5.2018	USD	CAD
415025	21.5.2018	USD	USD
415028	21.5.2018	RMB	USD
415028	21.5.2018	USD	USD
306163	21.5.2018	CAD	USD
306603	21.5.2018	USD	USD

Re: exclude matching data

MarcoWedel — Tue, 22 May 2018 21:29:49 GMT

Hi,

one solution might be:

='=Order_Date=Today()-1 and PO_Currency<>HomeCurrency'

hope this helps

regards

Marco

Re: exclude matching data

Anonymous — Wed, 23 May 2018 00:58:50 GMT

This looks like it would be right. I will check it out tomorrow. I had been missing the first = signs before Order_Date.

Thanks much...

Chuck

Re: exclude matching data

Anonymous — Wed, 23 May 2018 14:55:08 GMT

It turns out the date format is an issue and was able to solve by this:

=Date(Today()-2,'D.M.YYYY') and -1 didn't have any data yet...timing issue.

Then for Currency the formula just doesn't work for me. It should I think but not.

I have tried an alternate where Select Currency as =only(HomeCurrency)

Then Select Exclude (Currency). This being a global dataset, but my HomeCurrency is USD and it excludes all USD transactions for other countries.

I wish I understood why the <> formula does not work or why Match or Not Match wouldn't work either.

Half way there though!

Thanks...

Re: exclude matching data

Anonymous — Wed, 23 May 2018 18:31:14 GMT

Further info now...this formula for Currency<>HomeCurrency works fine in the desktop tool...but not on our server system. Not sure why that would be. I assumed you got your result on a desktop system so tried and it does work....the full statement works.

Any thoughts why a server version is different? It locks up the browser there.