Transform and Cleanse - stuck on how to use "Grouping" with condition from more than one column.

Toad Data Point & Toad Intelligence Central
1

I have been working with and successfully used the Transform and Cleanse module to Group columns based on the value of the column being used but am stuck on a case where the condition of two columns is used.

“Forc_Period_End_Dt” and “Cust_Coeff_On” are the names of the columns where the value is being evaluated and I want to create a column called “Algorithm”.

I am trying to run a query off an SQL database in Toad Data Point.

If the Forc_Period_End_Dt > 0 and the Cust_Coeff_On = Y then show “Enhanced - CC” in the Algorithm column

If Forc_Period_End_Dt > 0 and Cust_Coeff_On <> Y then show “Enhanced - OpCo” in the Algorithm column

Else show “Linear” in the Algorithm column.

Using the T&C module is my preference for this case but, if this cannot be done in that module, would someone please assist me in adding it to the Select statement?

Thank you in advance for any feedback/assistance.

2

You can try:

SELECT

CASE

WHEN Forc_Period_End_Dt > 0 and the Cust_Coeff_On = Y then “Enhanced - CC”

WHEN Forc_Period_End_Dt > 0 and Cust_Coeff_On <> Y then “Enhanced - OpCo”

Else Linear

END Algorithm

3

Thanks you for the quick response!

I get Lookup Error
ORA-00904: “Linear”: invalid identifier

4

You have to complete the select statement by using a FROM clause in your query. You need a table to pull the data from.

5

The invalid qualifier error means that you forgot to qualify the Linear text value in apostroph / quotes… So instead of Linear write ‘Linear’

6

Perfect! I got it to work. I was using “” instead of ‘’ and I changed the > 0 part to IS NULL since it is a date field. I really appreciate the help. That was making me crazy.