Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

Convert Datediff from SQL to Qlikview

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

5 Replies
zhadrakas
Valued Contributor II

Re: Convert Datediff from SQL to Qlikview

please try like this.

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

Partner
Partner

Re: Convert Datediff from SQL to Qlikview

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
Valued Contributor II

Re: Convert Datediff from SQL to Qlikview

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')

Partner
Partner

Re: Convert Datediff from SQL to Qlikview

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
Valued Contributor II

Re: Convert Datediff from SQL to Qlikview

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')