Combine date fields

[#1]

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

[its_anandrjs]

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]

[#3]

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

[its_anandrjs]

You required canonical Dates also let me know this Left Join on tables is working for you.