topic Re: Date greater than IF statement in QlikView

Date greater than IF statement

Anonymous — Mon, 09 Jan 2017 22:19:28 GMT

Hi All,

I need to select data based on ServiceDate less than CheckinDate so I am trying to have a listbox with expression

IF(ServiceDate<CheckinDate,'Y','N'). However, that is not doing the right selection.

Below is how I have the script in the SQL:

cast(ServiceDateTime as date) as ServiceDate,

cast(CheckinDate as date) as CheckinDate

Then on the chart properties-Number, I am selecting "M/D/YYYY".

Could someone please help so that I can select the data where ServiceDate>CheckinDate?

Thank you.

Re: Date greater than IF statement

sunny_talwar — Mon, 09 Jan 2017 22:22:20 GMT

May be create this logic in the script

If(ServiceDate > CheckinDate, 'Y', 'N') as Flag

and then use Flag as a way to filter?

Re: Date greater than IF statement

Anonymous — Mon, 09 Jan 2017 22:25:01 GMT

Hi Sunny,

I was thinking about that but it is complicated to do it in the script since I am pulling whole sets of different tables in 2 different tabs in QlikView. It seems like the IF statement is confusing because of the format may be.

Thank you.

Re: Date greater than IF statement

Anonymous — Mon, 09 Jan 2017 22:26:11 GMT

Let me try to play and edit the SQL script.

Re: Date greater than IF statement

sunny_talwar — Mon, 09 Jan 2017 22:27:26 GMT

The two dates are coming from different tables?

Re: Date greater than IF statement

Anonymous — Mon, 09 Jan 2017 22:31:46 GMT

Yes. The 2 dates are coming from the different tables but I used the same cast function in SQL so I am confused what is still wrong. And, I now see there are some NULL ServiceDates as well to make it even more complicated.

Re: Date greater than IF statement

sunny_talwar — Mon, 09 Jan 2017 22:33:33 GMT

Create a table box object and see if ServiceDate and CheckinDate have one to one relation. If they don't then your if statement is not going to work unless you use Aggr statetment here (I think)

Re: Date greater than IF statement

Anonymous — Mon, 09 Jan 2017 22:34:20 GMT

Yes. It does have one to many but shouldn't that still work? The CheckinDate will be only 1 specific. However, the ServiceDate is pulling all the charges on several ServiceDate of the same visit. So I need all the charges that has ServiceDate greater than AdmitDate.

Re: Date greater than IF statement

sunny_talwar — Mon, 09 Jan 2017 22:36:05 GMT

You can try this, but selections will be problematic with this

Aggr(If(ServiceDate>CheckinDate,'Y','N'), ServiceDate, CheckinDate)

Re: Date greater than IF statement

Anonymous — Mon, 09 Jan 2017 22:38:34 GMT

Yup. Not selecting it right.

Re: Date greater than IF statement

Anonymous — Mon, 09 Jan 2017 22:45:06 GMT

No worries Sunny. I will just export the data in Excel and manually do it in Excel. I am getting off.

Thanks much for your time again.

Re: Date greater than IF statement

sunny_talwar — Mon, 09 Jan 2017 22:45:13 GMT

Yes, I know it would be a problem.... only way would be to join the two tables in the script to create a flag field

Re: Date greater than IF statement

Anonymous — Mon, 09 Jan 2017 23:13:23 GMT

‌Is there anything that is common between the fields like a room id and are the service dates fixed or do they vary like the check in date?

Re: Date greater than IF statement

Anonymous — Tue, 10 Jan 2017 13:44:37 GMT

Yes. VisitID is common between the two tables. ChecinDate is fixed per visit. However, service date can be several different one depending on when an amount was paid. Btw, I need amounts that were paid with service date prior to check in so I am about to update my original question. I have figured it out in Excel though. Thank you.

Re: Date greater than IF statement

Anonymous — Wed, 11 Jan 2017 18:04:17 GMT

Did you update your question? It sounds like something I have worked on in the past and I may be able to provide some info.