Skip to main content
To homepage To homepage
Solved

SQL Case

ignacio
Contributor
Forum|alt.badge.img+1

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

Best answer by Bernarddb

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.

View original
Did this topic help you find an answer to your question?

5 replies

Bernarddb
TimeXtender Xpert
Forum|alt.badge.img+4
  • Bernarddb
  • TimeXtender Xpert
  • 53 replies
  • Answer
  • August 21, 2023

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.

daniel
1 person likes this
  • daniel
    daniel
rvgfox
Problem Solver
Forum|alt.badge.img+4
  • rvgfox
  • Problem Solver
  • 222 replies
  • August 21, 2023

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

Thomas Lind
1 person likes this
  • Thomas Lind
    Thomas Lind
ignacio
Contributor
Forum|alt.badge.img+1
  • ignacio
  • Author
  • Contributor
  • 29 replies
  • August 21, 2023

@Bernarddb 

 

I followed your instruction:

Create a table incidentGroup 

 

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

Bernarddb
TimeXtender Xpert
Forum|alt.badge.img+4
  • Bernarddb
  • TimeXtender Xpert
  • 53 replies
  • August 22, 2023

@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
1 person likes this
  • ignacio
    ignacio
ignacio
Contributor
Forum|alt.badge.img+1
  • ignacio
  • Author
  • Contributor
  • 29 replies
  • August 22, 2023

Thank @Bernarddb, now is work 😂

Reply


Most helpful members this week

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings