topic Re: Convert Datediff from SQL to Qlikview in QlikView

Convert Datediff from SQL to Qlikview

kalyandg — Tue, 13 Feb 2018 07:39:08 GMT

Hi,

I am having a requirement, like convert SQL queries into Qlikview format and store it as QVD.

One of the where condition, like date difference query should be converted to Qlikview style.

datediff( day, InvDate , @StartDate ) <=0 and datediff( day, InvDate , @EndDate ) >=0

I have tried using

1. (date(InvOrderDate,'DD/MM/YYYY')>=$(vStartDate) and date(InvOrderDate,'DD/MM/YYYY')<=$(vEndDate))

2. ($(vStartDate)-date(InvOrderDate,'DD/MM/YYYY')<=0 and $(vEndDate)-date(InvOrderDate,'DD/MM/YYYY')>=0 )

But its not working.

please help.

Thanks,

Kalyan

zhadrakas — Tue, 13 Feb 2018 07:50:40 GMT

please try like this.

Interval(@EndDate-@StartDate,'d')

kalyandg — Tue, 13 Feb 2018 09:02:10 GMT

Hi Tim Driller,

I have tried like this

(Interval($(vStartDate)-date(InvOrderDate,'DD/MM/YYYY'))<=0 and Interval($(vEndDate)-date(InvOrderDate,'DD/MM/YYYY'))>=0)

but its not working

zhadrakas — Tue, 13 Feb 2018 09:06:45 GMT

1) note that you Need to write , 'd' to calculate the interval in days

2) the first date (in this case @EndDate) should always be the higher date

Interval(@EndDate-@StartDate, 'd')

kalyandg — Tue, 13 Feb 2018 09:11:33 GMT

Hi Tim,

I have tried like this

(Interval($(vStartDate)-date(InvOrderDate,'DD/MM/YYYY'),'d')<=0 and Interval($(vEndDate)-date(InvOrderDate,'DD/MM/YYYY'),'d')>=0)

actually, we are extracting data according to the Start and End date, with the "InvOrderDate" column.

User will enter the start date and end date in front end, according to that, data should extract.

Thanks,

Kalyan

zhadrakas — Tue, 13 Feb 2018 09:24:38 GMT

i think you Need to a it like this

(Interval(date(InvOrderDate,'DD/MM/YYYY')- $(vStartDate),'d')>=0 and Interval($(vEndDate)-date(InvOrderDate,'DD/MM/YYYY'),'d')>=0)

i would test it by creating text boxes with that interval expressions to see if it works as suspected

Interval(date(InvOrderDate,'DD/MM/YYYY')- $(vStartDate),'d')