Date greater than IF statement

[Anonymous]

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.

[sunny_talwar]

May be create this logic in the script

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

and then use Flag as a way to filter?

[Anonymous]

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.

[Anonymous]

Let me try to play and edit the SQL script.

[sunny_talwar]

The two dates are coming from different tables?

[Anonymous]

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.

[sunny_talwar]

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)

[Anonymous]

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.

[sunny_talwar]

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

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

[Anonymous]

Yup. Not selecting it right.