topic Trying to replicate SQL datepart coding in Qlikview in QlikView

Trying to replicate SQL datepart coding in Qlikview

Fri, 14 Jan 2011 13:24:40 GMT

Hi all,

I am attempting to replicate some SQL datepart coding in Qlikview in order to compare two dates from 2 different tables (discharge and carepisodes) from a ODBC database: . The SQL is as follows

datepart(year, discharge.disdatetime) = datepart(year, careepisodes.dateto),
datepart(month, discharge.disdatetime) = datepart(month, careepisodes.dateto),
datepart(day, discharge.disdatetime) = datepart(day, careepisodes.dateto);

Is there a datepart equivalent in Qlikview?

Thanks,

Matt

Trying to replicate SQL datepart coding in Qlikview

hector — Fri, 14 Jan 2011 13:32:31 GMT

Hi, you can read this SQL directly in QlikView, like this


CONNECT ..

SQL Select
.....
.....
from.....
where
datepart(year, discharge.disdatetime) = datepart(year, careepisodes.dateto),
datepart(month, discharge.disdatetime) = datepart(month, careepisodes.dateto),
datepart(day, discharge.disdatetime) = datepart(day, careepisodes.dateto);

And answering the other question, yes in qlikview there are year(), month() and day()

Rgds

PS. by the way, if the year, the month and the day is the same, can you replace that section with

discharge.disdatetime = careepisodes.dateto

or do you have the time (decimals) in this field also?

Trying to replicate SQL datepart coding in Qlikview

Fri, 14 Jan 2011 14:09:21 GMT

Thanks Hector.

The time is in the disdatetime field (i.e. 14/01/2011 13.00:59) but not in the dateto field (14/01/2011) so this is why I am attempting to match the date/month/year only.

Unfortunately I am unable to add the datepart coding into my Edit Script - it keeps coming back with error.

I already have the SQL select command for the careepisodes table and now I am trying to drag in the discharge table:

SQL SELECT patientid as personno,disdatetime, distype
FROM xxx.dbo.discharge

where datepart(year, discharge.disdatetime) = datepart(year, careepisodes.dateto),datepart(month, discharge.disdatetime) = datepart(month, careepisodes.dateto),datepart(day, discharge.disdatetime) = datepart(day, careepisodes.dateto);

I am stil getting to grips with Qlikview so appreciate this is probably the way I am doing it?

Thanks,

Matt

Trying to replicate SQL datepart coding in Qlikview

hector — Fri, 14 Jan 2011 18:48:04 GMT

Hi again

Please post the script here

By the way, i've just copied and pasted your code, but instead of "," in the where, replace it with and "AND"


where
datepart(year, discharge.disdatetime) = datepart(year, careepisodes.dateto) AND
datepart(month, discharge.disdatetime) = datepart(month, careepisodes.dateto) AND
datepart(day, discharge.disdatetime) = datepart(day, careepisodes.dateto);

maybe another approach it's just floor the date field (because the decimal part is the time fraction), so if you have


where floor(disdatetim) = datet

Rgds

Trying to replicate SQL datepart coding in Qlikview

Mon, 17 Jan 2011 11:56:25 GMT

Sorry Hector, it seems Qlikview does not recognise the datepart format in my Edit Script command.

In my Expression box where I wish to display the number of results: I have the following:

IF(discharged = 1,caretype = 'N', COUNT({< disdatetime = {'>=$(=Date(vStartdate))<=$(=Date(vEnddate))'} >}surname))

Is there an alternative date function where I can match day/month/year in here?

Trying to replicate SQL datepart coding in Qlikview

hector — Mon, 17 Jan 2011 18:07:27 GMT

Hi, can you upload an example qvw and dummy data?

rgds

Trying to replicate SQL datepart coding in Qlikview

Mon, 17 Jan 2011 20:20:07 GMT

Try using NUM() function

Ex: Num(disdatetime ), Num(Date(vStartdate))

Trying to replicate SQL datepart coding in Qlikview

Mon, 17 Jan 2011 21:03:40 GMT

Hi Hector,

Is there a reason that you need the DATEPART function? If not, you could just simplify the script to

FLOOR(CAST(discharge.disdatetime AS FLOAT)) = FLOOR(CAST(careepisodes.dateto AS FLOAT))

This will ignore the time, but compare everything else.

Regards,

Aline

Trying to replicate SQL datepart coding in Qlikview

Wed, 19 Jan 2011 17:10:08 GMT

Thanks all

Hector - Thanks but not sure how to upload example qvw with dummy data?

qlikviewgoer and Aline.Koch -

Thanks but should your coding be in the Edit Script function or within the Expressions of the Chart Properties?

I tried both and neither worked. When in the Edit Script function, it caused an error and did not recognise the CAST command nor the Num function.

The relevant tables from an ODBC database I am working with is careepisodes and discharges

From the careepisodes table, I am using

SQL SELECT patientid as personno, projectno, caretype, datefrom as residentdatefrom, dateto as residentdateto
FROM XXX.dbo.careepisodes where careepisodes.current_ = 1 and careepisodes.projectno not in (....);

From discharges tables, I am using

SQL SELECT patientid as personno,distype,disdatetime
FROM XXX.dbo.discharge;

Within the Expression in my Chart box<

IF(discharged = 1,caretype = 'N', COUNT({< disdatetime = {'>=$(=Date(vStartdate))<=$(=Date(vEnddate))'} >}surname))

( I am picking up the Surname field from another table which is working fine)

The above Expression in my chart box is bringing back approx 143 records when the true figure should be approx 93 from the equivalent SQL command. The SQL command uses the datepart command to match the date against disdatetime and dateto (now known as residentdateto in Qlikview) which is where, I think, the problem is as I seem to be unable to replicate this date match command in Qlikview

Trying to replicate SQL datepart coding in Qlikview

Wed, 19 Jan 2011 19:22:33 GMT

The formula with FLOOR & CAST is T-SQL for SQL Server and is done in the Edit Script Window.

ODBC is a common connection type that can be used with almost any type of modern database.

Your formula above with the Set Analysis should work. In the 93, are you counting both the start date and end dates as well?

If you can create some dummy data in Excel or a table, whichever is easier for you, bring it into a qvw, zip up the data & the qvw, then attach it by clicking the Options tab at the top and clicking on Add under File Attachment, it might be easier to see the issue.

Thanks,

Aline