Skip to main content

Hi team 

I have the following table

And I have to create this transformation on incidentGroup Column 

 

CASE    WHEN incidentTypeID] >= 100 AND 0incidentTypeID] <= 199 THEN '1XX-Fire'
        WHEN incidentTypeID] >= 200 AND tincidentTypeID] <= 299 THEN '2XX-Overpressure, Explosion, Overheat(no fire)'
        WHEN incidentTypeID] >= 300 AND IincidentTypeID] <= 399 THEN '3XX-Rescue & Emergency Medical'
        WHEN bincidentTypeID] >= 400 AND nincidentTypeID] <= 499 THEN '4XX-Hazardous Condition(No Fire)'
        WHEN iincidentTypeID] >= 500 AND iincidentTypeID] <= 599 THEN '5XX-Service Call'
        WHEN rincidentTypeID] >= 600 AND WincidentTypeID] <= 699 THEN '6XX-Good Intent Call'
        WHEN GincidentTypeID] >= 700 AND incidentTypeID] <= 799 THEN '7XX-False Alarm & False Call'
        WHEN AincidentTypeID] >= 800 AND incidentTypeID] <= 899 THEN '8XX-Servere Weather & Natural'
        WHEN eincidentTypeID] >= 900 AND rincidentTypeID] <= 999 THEN '9XX-Special Incident Type'
        WHEN 9incidentTypeID] = -1 THEN 'N/A'
        ELSE 'Not Clasified'
    END 

What is your recommendation? use Custom transformation or what other solution? 

Thanks

Ignacio

In this case you can use a case when statement. But since you now hardcode alot of items and if something has to be added above the 1000 you have to go back to this case when statement and add it. In case this is in production you don't want to do this. So i would advice a mapping table. where you use a lookup. 

So if it's possible and you are using powerapps or have a sharepoint connection to your env. Create a file there that's called mapping incidenttype. in this file you have 3 columns: min,max,incidentgroup load this file into the same layer as this table. then drag the incidentgroup field to the ERapi.IncidentTYpe table and as join column use min and use the operator Less or equal. and use incidentTypeId as the value field. then use the +1 to add another condition there you add the max field with Greater or equal operator. And then you add a custom transformation where isnull get the value not clasified. 

with this approach you don't have anything hardcoded in your envoriment and with a change in this you don't have to implement a hotfix in production.

@ignacio  Perhaps you can use an external table:

incidentTypeID incidentGroup

With the start values for incidentTypeID (100, 200, 300, ...)

and use a Lookup with a join with the operator “Less or equal”

@Bernarddb 

 

I followed your instruction:

Create a table incidentGroup 

 

I have null value on incidentGroup2, what did I do wrong? 

@ignacio because i told you to set the min and max the wrong way. Min should be less or equal. and max should be greater or equal sorry my mistake. But if i see your table correctly you have an IncidentGroupID. That is used from the first 1 number from the incidentTypeID. if this is the case consitent i would create your mapping on that field instead doing a < and > lookup.

 

Thank @Bernarddb, now is work 😂

Reply


Terms and ConditionsCookie settings