Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Combine date fields

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

3 Replies

Re: Combine date fields

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]

Not applicable

Re: Combine date fields

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

Re: Combine date fields

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