topic Re: Date issue with if statement in QlikView

Date issue with if statement

davyqliks — Sat, 16 Nov 2024 01:56:03 GMT

Hi All,

Thank you in advance for trying to assist with my if statement.

I have an issue where i am using 1 of 2 dates for my imported sales data.

The logic should be use [Issue date] if greater than [Voucher Date] or if [Voucher Date] is blank, as [Voucher Date], else use voucher date.

We are importing daily files, appending to an incrementally loaded qvd and then placing the file in a done folder until a new file is received the next day.

The file is working and updating but the date using the below is using [Issue date], even if the [Voucher Date] is greater.

here is the line of code:

if(([Issue date]>=[Voucher Date] or len([Voucher Date]=0)), Date([Issue date], 'DD/MM/YYYY'),Date([Voucher Date], 'DD/MM/YYYY')) as [Voucher Date],

Please can any of the experts of the qlik world advise why,

If the [Voucher Date] is greater than the [Issue date], that the [Voucher Date] is not being used.

NOTE:

If there is a [Voucher Date] present it will always be greater than [Issue date]

Thank you again for your time and expertise.

Regards,

Daniel

Re: Date issue with if statement

mahaveerbiraj — Fri, 15 Nov 2019 12:22:14 GMT

as you said ' If there is a [Voucher Date] present it will always be greater than [Issue date] '

then Just Use like below

if(( len([Voucher Date]=0)), Date([Issue date], 'DD/MM/YYYY'),Date([Voucher Date], 'DD/MM/YYYY')) as [Voucher Date],

Still you want use both condition try like below,

if(( num([Issue date])>=num([Voucher Date]) or len([Voucher Date]=0)), Date([Issue date], 'DD/MM/YYYY'),Date([Voucher Date], 'DD/MM/YYYY')) as [Voucher Date],

use num() function while comparing

Re: Date issue with if statement

sunny_talwar — Fri, 15 Nov 2019 12:30:50 GMT

1st thing I would check is if your date field is read as date or not... if not, then you might need to use Date#() for Qlik to understand your date field as a date field. Once you fix this part, the expression can be simplified to this

Date(RangeMax([Issue date], [Voucher Date]), 'DD/MM/YYYY') as [Voucher Date]

Re: Date issue with if statement

davyqliks — Fri, 15 Nov 2019 15:24:49 GMT

Hey Sunny,

You simplified it somewhat!

Thank you so much, again 🙂 you nailed it for me.

Much appreciated

Daniel