Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a scenario in Qlik sense that I am extracting the facility field from sensor group, the problem is that if that sensor group is deleted then we are extracting location from Incident that is created by the sensor which is deleted.
I am trying to implement logic like if the sensorID is available in the sensor table then it should take the facility from sensor table and if it's deleted then it should take the Facility from Incident Table.
Your suggestions would be highly appreciated...
Does this what you need?
If(IsNull([SensorTable.Facility]), [IncidentTable.Facility], [SensorTable.Facility])
Hi @LRuCelver ,
it should be like if SensorID in the incident table is available in the sensor table.SensorID
here the sensor table data is current data and incident data is like today or created long back
fpr example sensorid 23407 created incident yesterday and today we delted that sensor (in this case the incident created in the past dont have the loaction as it will be unavailable after deleting that sensorid,hence we will fectch location from Incident_name created out of that Sensorid).
Here are a few examples since I don't know the structure of your data model:
Using this expression will result in the 4th column:
If(IsNull(SensorFacility), IncidentFacility, SensorFacility)
In this case we need to find the most recent date and go select the field accordingly:
If(RangeMax(Max(SensorDate), 0) >= RangeMax(Max(IncidentDate), 0), FirstSortedValue(SensorFacility, -SensorDate), FirstSortedValue(IncidentFacility, -IncidentDate))
Left: Datamodel; Right: Result:
In this case we can apply the expression from case 1 to all dates and select the one from the most recent date:
FirstSortedValue(If(IsNull(SensorFacility), IncidentFacility, SensorFacility), -Date)
Left: Datamodel; Right: Result:
1:
SensorData:
NoConcatenate Load * Inline [
SensorID, SensorFacility
1, A
2, B
];
IncidentData:
NoConcatenate Load * Inline [
SensorID, IncidentFacility
2, C
3, D
];
2:
SensorData:
NoConcatenate Load * Inline [
SensorDate, SensorID, SensorFacility
20240301, 1, A
20240302, 1, B
20240301, 2, B
20240302, 3, B
20240301, 4, B
];
IncidentData:
NoConcatenate Load * Inline [
IncidentDate, SensorID, IncidentFacility
20240302, 2, C
20240301, 3, C
20240301, 4, C
20240301, 5, C
20240302, 5, D
];
3:
SensorData:
NoConcatenate Load
*,
AutoNumberHash128(Date, SensorID) as DateSensorKey
Inline [
Date, SensorID, SensorFacility
20240301, 1, A
20240302, 1, B
20240301, 2, B
20240302, 3, B
20240301, 4, B
];
IncidentData:
NoConcatenate Load
*,
AutoNumberHash128(Date, SensorID) as DateSensorKey
Inline [
Date, SensorID, IncidentFacility
20240302, 2, C
20240301, 3, C
20240301, 4, C
20240301, 5, C
20240302, 5, D
];
LinkTable:
NoConcatenate Load Distinct
Date,
SensorID,
DateSensorKey,
DateSensorKey as Temp_DateSensorKey
Resident SensorData;
Concatenate Load Distinct
Date,
SensorID,
DateSensorKey,
DateSensorKey as Temp_DateSensorKey
Resident IncidentData
Where not Exists(Temp_DateSensorKey, DateSensorKey);
Drop Field Temp_DateSensorKey;
Drop Fields Date, SensorID From SensorData, IncidentData;
Hi @LRuCelverLRuCelver,
Thanks for the hint, but we don't have any date field in the sensor table, only incidents table we are extracting from creation time of each Incident.
Thanks.