6 Replies Latest reply: Apr 19, 2016 10:15 AM by Sunny Talwar RSS

    MakeDate in script

    Luca V

      Hi,

      i have this script:

       

      RATE_HOUR_DATA:

      SELECT PRO.`IDPROFILE` AS `IDPROFILERATE`,

      MakeDate(P.`YEAR`,P.`MONTH`,P.`DAY`) as DATETIME,

           `YEAR` AS `YEAR`,

           `MONTH` AS `MONTH`,

           `DAY` AS `DAY_NUM`,

           `HOUR` AS `HOUR`,

           `RATE` AS `RATE`

         FROM  `po_hour` P

         LEFT JOIN `profile` PRO

      on PRO.`IDPROFILE` = P.`IDPROFILE`   ;

       

      Can you tell me where is wrong ?

      I also would like to add the HOUR into DATETIME, how can i do ?

      Qlikview send me this messagge:

       

      SQL##f - SqlState: S1000, ErrorCode: 1582, ErrorMsg: [MySQL][ODBC 5.1 Driver][mysqld-5.5.32]Incorrect parameter count in the call to native function 'MakeDate'

      RATE_HOUR_DATA:

      SELECT PRO.`IDPROFILE` AS `IDPROFILERATE`,

      MakeDate(P.`YEAR`,P.`MONTH`,P.`DAY`) as DATETIME,

           `YEAR` AS `YEAR`,

           `MONTH` AS `MONTH`,

           `DAY` AS `DAY_NUM`,

           `HOUR` AS `HOUR`,

           `RATE` AS `RATE`

         FROM  `po_hour` P

         LEFT JOIN `profile` PRO

      on PRO.`IDPROFILE` = P.`IDPROFILE`

        

       

      Thanks

        • Re: MakeDate in script
          Sunny Talwar

          MakeDate is a QlikView function, try this:

           

          RATE_HOUR_DATA:

          LOAD *,

          MakeDate(`YEAR`, `MONTH`, `DAY`) as DATETIME;

          SELECT PRO.`IDPROFILE` AS `IDPROFILERATE`,

              `YEAR` AS `YEAR`,

              `MONTH` AS `MONTH`,

              `DAY` AS `DAY_NUM`,

              `HOUR` AS `HOUR`,

              `RATE` AS `RATE`

            FROM  `po_hour` P

            LEFT JOIN `profile` PRO

          on PRO.`IDPROFILE` = P.`IDPROFILE`;

          • Re: MakeDate in script
            Ruben Marin

            Hi Luca, MakeDate() is QV Function, ...

             

            Edit: Sunny was faster

            • Re: MakeDate in script
              sreemannarayana g

              Hi Luca,

              In MYSQL, MAKEDATE function will have parameters like "Year" and "Day of the Year" [ MAKEDATE( year, day-of-year )] which means if the Month is feb and Date is 10 then the function would be MAKEDATE(Year, 41). If you want to convert the Year, Month, Day to date then try to use the STR_TO_DATE function in mysql.

               

              Thanks,
              Sreeman.

              • Re: MakeDate in script
                Stefan Wühl

                And if you want to add hour:

                 

                RATE_HOUR_DATA:

                LOAD *,

                          Timestamp(Makedate(YEAR, MONTH, DAY_NUM)+MakeTime(HOUR)) as DATETIME;

                SELECT PRO.`IDPROFILE` AS `IDPROFILERATE`,

                MakeDate(P.`YEAR`,P.`MONTH`,P.`DAY`) as DATETIME,

                     `YEAR` AS `YEAR`,

                     `MONTH` AS `MONTH`,

                     `DAY` AS `DAY_NUM`,

                     `HOUR` AS `HOUR`,

                     `RATE` AS `RATE`

                   FROM  `po_hour` P

                   LEFT JOIN `profile` PRO

                on PRO.`IDPROFILE` = P.`IDPROFILE`   ;

                 

                The Timestamp() function will format according your default timestamp format setting (or use an additional format code as second argument).

                • Re: MakeDate in script
                  Luca V

                  Thanks, this works:

                  Can you please tell me how to add HOUR to DATETIME ?

                   

                   

                  RATE_HOUR_DATA_TEMP:

                  SELECT PRO.`IDPROFILE` AS `IDPROFILERATE`,

                       `YEAR` AS `YEAR`,

                       `MONTH` AS `MONTH`,

                       `DAY` AS `DAY`,

                       `HOUR` AS `HOUR`,

                       `RATE` AS `RATE`

                     FROM  `po_hour` P

                     LEFT JOIN `profile` PRO

                  on PRO.`IDPROFILE` = P.`IDPROFILE`;

                   

                   

                  RATE_HOUR_DATA:

                  LOAD *,

                  MakeDate(YEAR,MONTH,DAY) as DATETIME

                  Resident RATE_HOUR_DATA_TEMP;

                    • Re: MakeDate in script
                      Sunny Talwar

                      Stefan just mentioned in his response above

                      Timestamp(Makedate(YEAR, MONTH, DAY_NUM)+MakeTime(HOUR)) as DATETIME;

                       

                      For your resident load, like this:

                       

                      RATE_HOUR_DATA:

                      LOAD *,

                      TimeStamp(MakeDate(YEAR, MONTH, DAY_NUM)+MakeTime(HOUR)) as DATETIME;

                      Resident RATE_HOUR_DATA_TEMP;