9 Replies Latest reply: Jan 19, 2011 2:22 PM by Aline.Koch RSS

    Trying to replicate SQL datepart coding in Qlikview

      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

          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

              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

                  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

                      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

                        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

                            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

                                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

                      • Trying to replicate SQL datepart coding in Qlikview
                        Anil Konduri

                        Try using NUM() function

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