topic Re: OR Logic using Button in QlikView

OR Logic using Button

Wed, 09 Sep 2015 17:42:49 GMT

Hi,

We are converting some Business objects reports, using a list of filters in Qlikview. I am having a button in qlikview for each report. The button has the list of conditions inbuilt so when a user clicks on the button, the report gets generated as per the conditions given inside the button.

Couple of issues here:

1. Date compare. I have this in Field

=date(TEST_EXPIRATION_DT)

I have given this in Search String

=date(now(),'MM/DD/YYYY')

but it's not working. Basically I want to filter out records when the test_expiration_dt matches sysdate/current date

2. How to handle OR conditions. I don't see an option for this within Button. Do we have any other way to handle the combination of 'OR' and 'AND' conditions?

Re: OR Logic using Button

dclark0699 — Wed, 09 Sep 2015 18:51:09 GMT

The "Field" field of the button action is going to be looking for a an actual field in the data model. So I don't think you can put a function on the field here. I could be wrong.

Try Field = TEST_EXPIRATION_DT

Search String = NUM(TODAY())

You can remove the NUM() if TEST_EXPIRATION_DT is not a number in the data model.

Not sure what you mean by AND/OR conditions without an example. You can do advanced searches within a field that can do all kinds of extensive logic though. For example, searching in an ID column with "=FIELD_A = 'YES' OR FIELD_B = 'YES'"

Re: OR Logic using Button

Wed, 09 Sep 2015 18:59:52 GMT

Thanks for your reply. I tried but it does not work. It is a date datatype so I had removed num() and still it did not work. Please assist.

Thanks,

Sudha.

Re: OR Logic using Button

dclark0699 — Wed, 09 Sep 2015 19:01:26 GMT

Would you be able to attach a sample qvw? You can scrub out any sensitive information, but it's helpful to make sure I understand exactly what you are trying to do

Re: OR Logic using Button

Wed, 09 Sep 2015 19:34:43 GMT

!It's a date datatype coming from a database table so I am not sure how to attach the qvw. But I just did as you had demonstated.

Re: OR Logic using Button

dclark0699 — Wed, 09 Sep 2015 19:39:12 GMT

Try this

=DATE(FLOOR(TODAY()))

This works in my data model where I have the date as a number =FLOOR(TODAY())

Re: OR Logic using Button

sasiparupudi1 — Wed, 09 Sep 2015 19:44:42 GMT

It looks like your field is a timestamp.

Try Field = TEST_EXPIRATION_DT

Search String = Timestamp(now(),YourtimestampFormat)

hth

Sasi

Re: OR Logic using Button

Wed, 09 Sep 2015 20:19:25 GMT

Hi,

You are right. Though it's a date datatype, it appears like a timestamp value. I tried what you had suggested and it does not work still.

Re: OR Logic using Button

dclark0699 — Wed, 09 Sep 2015 21:06:07 GMT

Ah I see the problem from that screenshot. What you are searching for there is the exact moment you click the button. But it sounds like you want any records that have occured at any point during the day, correct?

So you should be about to put in the search field

= '>' & TIMESTAMP(FLOOR(TODAY()))

This would be telling it to search for any value that occured since the beginning of the day

Re: OR Logic using Button

Thu, 10 Sep 2015 13:38:57 GMT

Thank you so much! This is working

Re: OR Logic using Button

Thu, 10 Sep 2015 13:46:17 GMT

But this gives me all the dates that are greater than today. I want to restrict it to just one day i.e today. I tried '>=' in the above expression. It looks fine but it is filtering out all dates greater than today. Please help me how to handle '>='

Re: OR Logic using Button

sasiparupudi1 — Thu, 10 Sep 2015 13:53:35 GMT

Try

= '>' & TIMESTAMP(FLOOR(TODAY())) and '<=' & TIMESTAMP(DayEnd(TODAY()))

hth

Sasi

Re: OR Logic using Button

Thu, 10 Sep 2015 14:01:59 GMT

The Expression looks OK but it does not work.

Re: OR Logic using Button

dclark0699 — Thu, 10 Sep 2015 14:08:33 GMT

I didn't realize there would be dates greater than today in there. I would encourage you to create a field in your data model via the script that is just the date.

FLOOR(TEST_EXPIRATION_DT) as TEST_EXPIRATION_DATE

Then use the button to select in that field instead

Re: OR Logic using Button

sasiparupudi1 — Thu, 10 Sep 2015 14:13:40 GMT

Please try one more time

= '>=' & TIMESTAMP(FLOOR(TODAY())) & ' and <=' & TIMESTAMP(DayEnd(TODAY()))

Re: OR Logic using Button

jonathandienst — Thu, 10 Sep 2015 14:25:08 GMT

Today() does not contain time information and the comparison should be on the value of the date, so all he Floor()ing and formatting is unnecessary and just confusing the issue. Something like this should work:

>=(Today()) Ans <(Today() + 1)

Re: OR Logic using Button

Thu, 10 Sep 2015 14:25:21 GMT

Hi,

The expression is valid and it is even appearing correctly in 'Current Selection'; however, the data is not filtered as per the range we specify.

However converting the original timestamp column to date and then using a simple equal to is working fine. So problem solved

Thank you very much!

Re: OR Logic using Button

Thu, 10 Sep 2015 14:26:15 GMT

That's true. I too realized it. Thanks!

Re: OR Logic using Button

Thu, 10 Sep 2015 14:27:31 GMT

Hi,

Thank you very much! This suggestion works! I converted the timestamp to date column, so specifying a date range is not necessary now.