3 Replies Latest reply: Sep 4, 2017 7:52 AM by Anand Chouhan RSS

    Combine date fields

    Duckin Lama

      Hello All,

       

      I have two tables. Alert table and Parameter table. Sample data is as shown. I want to create a master table combining both the date fields. Both tables have a common field 'Device ID'. My idea is to select a date and show the range of temperature and humidity values from the Parameter table as well as the alerts from the device ID.  So I wish to put my date fields in the master calendar

       

                                  Alert Table

      Device IDAlert NameDateAlert Value
      a11Temperature Alert21-08-201732
      a12Temperature Alert22-08-201733
      a13Humidity Alert23-08-201720
      a13Temperature Alert23-08-201735
      a14Temperature Alert24-08-201734

       

       

       

                                Parameter table

      Device IDParameter NameDateValue
      a11Temperature21-08-201725
      a11Temperature21-08-201732
      a11Humidity21-08-201735
      a12Temperature22-08-201733
      a12Temperature22-08-201724
      a12Humidity 21-08-201740
      a13Humidity 23-08-201742
      a13Humidity 23-08-201720
      a13Temperature23-08-201735
      a13Temperature23-08-201725
      a14Temperature24-08-201734

       

       

      p.s : This is just an extract/part of my data. There are many more tables with Device ID as a common field which I will further include in this.

       

      Regards

        • Re: Combine date fields
          Anand Chouhan

          Try with this and after that use the Date fields, Value Or [Alert Value]

           

          [Alert Table]:

          Load

          [Device ID] &'|'& Date as %Key,

          [Parameter Name],

          Date as Device_Date,

          Value

          From [Parameter table];

           

          Left Join([Parameter Table])

          Load

          [Device ID] &'|'& Date as %Key,

          [Alert Name],

          Date as Alert_Date,

          [Alert Value]

          From [Alert Table]

            • Re: Combine date fields
              Duckin Lama

              Hi Anand,

               

              Thanks for replying back. This seems to be correct but I actually wanted to do this using master calendar where I can combine the dates of both the tables using a common date field. Say, if I have a date in the parameter table '27-08-2017'  but on the same date there was no alert so I dont have a date entry for this particular date in the alert table.

               

              I have two line charts, one in which I show the alert trend across different dates and in the other, I show the actual readings(value) of the temperature and humidity. Now when I select the date '27-08-2017' , I should be able to show 0 alert for that date and the actual readings from the parameter table