Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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
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?
Hi, can you upload an example qvw and dummy data?
rgds
Try using NUM() function
Ex: Num(disdatetime ), Num(Date(vStartdate))
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
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
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