Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

rakmuperr
New Contributor

exclude matching data

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

1 Solution

Accepted Solutions

Re: exclude matching data

Hi,

one solution might be:

QlikCommunity_Thread_302286_Pic1.JPG

QlikCommunity_Thread_302286_Pic2.JPG

QlikCommunity_Thread_302286_Pic3.JPG

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

QlikCommunity_Thread_302286_Pic4.JPG

hope this helps

regards

Marco

View solution in original post

6 Replies
isingh30
Valued Contributor

Re: exclude matching data

Please share sample application or data in excel sheet?

Thank you!

rakmuperr
New Contributor

Re: exclude matching data

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_DatePO_CurrencyHomeCurrency
41475121.5.2018USDUSD
30332821.5.2018EUREUR
30332821.5.2018EURUSD
30369921.5.2018CADUSD
30419621.5.2018USDUSD
30430621.5.2018USDRMB
30434921.5.2018USDUSD
30448421.5.2018CADCAD
30458021.5.2018USDUSD
30458021.5.2018USDUSD
41474021.5.2018EURRMB
41474021.5.2018USDEUR
41474921.5.2018USDUSD
30563221.5.2018EUREUR
41474321.5.2018USDUSD
41502421.5.2018USDCAD
41502521.5.2018USDUSD
41502821.5.2018RMBUSD
41502821.5.2018USDUSD
30616321.5.2018CADUSD
30660321.5.2018USDUSD

Re: exclude matching data

Hi,

one solution might be:

QlikCommunity_Thread_302286_Pic1.JPG

QlikCommunity_Thread_302286_Pic2.JPG

QlikCommunity_Thread_302286_Pic3.JPG

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

QlikCommunity_Thread_302286_Pic4.JPG

hope this helps

regards

Marco

View solution in original post

rakmuperr
New Contributor

Re: exclude matching data

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

rakmuperr
New Contributor

Re: exclude matching data

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...

rakmuperr
New Contributor

Re: exclude matching data

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.