topic Re: Location Deatils in App Development

Location Deatils

Raju_6952 — Wed, 06 Mar 2024 08:45:31 GMT

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

Re: Location Deatils

LRuCelver — Wed, 06 Mar 2024 08:52:09 GMT

Does this what you need?

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

Re: Location Deatils

Raju_6952 — Wed, 06 Mar 2024 09:38:58 GMT

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

Re: Location Deatils

LRuCelver — Wed, 06 Mar 2024 10:25:43 GMT

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

SensorData: NoConcatenate Load * Inline [ SensorID, SensorFacility 1, A 2, B ]; IncidentData: NoConcatenate Load * Inline [ SensorID, IncidentFacility 2, C 3, D ];

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 ];

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;

Re: Location Deatils

Raju_6952 — Wed, 06 Mar 2024 11:59:45 GMT

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.