To homepage To homepage
Solved

SQL Case

  • 18 August 2023
  • 5 replies
  • 87 views
ignacio
Rank
Badge

Hi team 

I have the following table

And I have to create this transformation on incidentGroup Column 

 

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

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

Thanks

Ignacio

icon

Best answer by Bernarddb 21 August 2023, 07:51

View original

5 replies

Bernarddb
Rank
Userlevel 1
Badge

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.

rvgfox
Rank
Userlevel 3
Badge +1

@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”

ignacio
Rank
Badge

@Bernarddb 

 

I followed your instruction:

Create a table incidentGroup 

 

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

Bernarddb
Rank
Userlevel 1
Badge

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

 

ignacio
Rank
Badge

Thank @Bernarddb, now is work 😂

Reply


Terms and ConditionsCookie settings