Location Deatils

[Raju_6952]

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...

[LRuCelver]

Does this what you need?

If(IsNull([SensorTable.Facility]), [IncidentTable.Facility], [SensorTable.Facility])

[Raju_6952]

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).

[LRuCelver]

Here are a few examples since I don't know the structure of your data model:

1. No dates; Tables are linked via SensorID

Using this expression will result in the 4th column:

If(IsNull(SensorFacility), IncidentFacility, SensorFacility)

2. Separate dates; Tables are linked via SensorID

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:

3. Tables are linked via Date and SensorID

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:

Load Scripts for all examples

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;

[Raju_6952]

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.