SSIS equivalent of a case when
-
Hi guys new poster here so treat me gently please đŸ™‚
I am writing an ETL routing with SSIS and I have got stuck with some conditional splits and how to handle them.There is a column called "S" in the raw data that has various stati that the call can be at.
Examples:Answered Tr/Pk/Rc/Pu Diverted TrTo Failed Advanced [UnMon Ent] P/U From Grp ABANDONED I then lookup S in a translation table as follows
S Derived_Status
Answered PRESENTED Tr/Pk/Rc/Pu PRESENTED Diverted DIVERTED TrTo TRANSFERRED Failed FAILED Advanced PRESENTED [UnMon Ent] FAILED P/U From Grp PRESENTED ABANDONED ABANDONED The issue I have is that I need to calculate an additional status based on the value in anther field.
The output will be 2 derived columns
called
FINAL_STATUS_45 and FINAL_STATUS_60The 45 and 60 relate to the length of time the call rang for before being answered.
So
if a call rings for less than 45 seconds and has a derived status of ABANDONED, I want FINAL_STATUS_45 to take the value "QUICK ABANDONED" ELSE take the value of DERIVED_STATUS
if a call rings for less than 60 seconds and has a derived status of ABANDONED, I want FINAL_STATUS_60 to take the value "QUICK ABANDONED" ELSE take the value of DERIVED_STATUSI am struggling to work out how to add in the "QUICK ABANDONED" as the result of the conditional split.
My conditional split expressions are so far:
Can anyone help me on how to achieve this please?
Regards
Dave
-
Hi, I have come up with a different solution, not quite as sustainable with high data voulmes but as the volume will be relatively low for this process, I have the data source as an SQL statement and used case whens so that the fields exist when the data is imported into the data flow.
What are the pitfalls of this?Cheers
Dave
-
Hey David,
It sounds like you may have already started to explore an alternate solution, but I didn't see you mention the ternary operator in SSIS, which operates like : [Condition Statement] ? [True outcome] : [False outcome]. You can chain them together to put another condition in the [False outcome] section to get multiple branches/cases to get what you need. In my understanding, a Derived column that results in a value being placed into a column might be more straight forward than doing a conditional split and sending the data in different directions. That last part is possibly not applicable, just trying to draw some conclusions on what you have presented. Meanwhile, if you were already aware of and are using the ternary operator, my suggestion probably doesn't help much, but, personally, when I think of a Case statement, I find myself using the ternary operator in a derived column rather than using the conditional split, unless I truly need to send data to different destinations and data flow transformations. I hope that helps.
-
Hi cwe242 thanks for the suggestion. I shall try it tonight and let you know how it works. Regards
Dave
- david_h_edmonds - Tuesday, February 20, 2018 9:57 AM
This is a decent solution ... there are no particular pitfalls.
I would, however, suggest that you consider creating 'DerivedStatus' as a permanent physical mapping table and using that in your query, rather than CASE.
If you decide that you really want to code all of this in SSIS instead, it would be simple enough to write a few lines of code in a Script Component to implement the logic for you. Much easier to read and understand (IMO) than a derived column containing multiple nested (condition?True:False) expressions.The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil, thanks for the reply. Derived_Status is already a mapping table field that I am joining to in the SQL, the case when is simply:
Case when Derived status = ‘abandoned’ and ring_ secs <= 45 then ‘Quick Abandoned’ else Derived_Status End
and the same for the 60 second field
- david_h_edmonds - Tuesday, February 20, 2018 11:51 AM
Cool: nice and simple.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply