Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ID | Alert Name | Date | Alert Value |
---|---|---|---|
a11 | Temperature Alert | 21-08-2017 | 32 |
a12 | Temperature Alert | 22-08-2017 | 33 |
a13 | Humidity Alert | 23-08-2017 | 20 |
a13 | Temperature Alert | 23-08-2017 | 35 |
a14 | Temperature Alert | 24-08-2017 | 34 |
Parameter table
Device ID | Parameter Name | Date | Value |
---|---|---|---|
a11 | Temperature | 21-08-2017 | 25 |
a11 | Temperature | 21-08-2017 | 32 |
a11 | Humidity | 21-08-2017 | 35 |
a12 | Temperature | 22-08-2017 | 33 |
a12 | Temperature | 22-08-2017 | 24 |
a12 | Humidity | 21-08-2017 | 40 |
a13 | Humidity | 23-08-2017 | 42 |
a13 | Humidity | 23-08-2017 | 20 |
a13 | Temperature | 23-08-2017 | 35 |
a13 | Temperature | 23-08-2017 | 25 |
a14 | Temperature | 24-08-2017 | 34 |
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
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]
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
You required canonical Dates also let me know this Left Join on tables is working for you.