Computed Columns

[AnnaBetts]

I am a new user and I have created a number of new computed columns through the Query Builder.  When I create a new computed column based on an existing computed column, the full text of the initial computed column is brought in (rather than a reference to the name of the column).  I had since a reason to change one of the initial calculations, but I then found that the other columns are not automatically updated as it keeps the initial text.  Is there a way to reference the cell so that further changes can be picked up throughout the model?

Thanks a lot,  Anna

[Reeza]

I'm having a hard time following this, can you post a picture that illustrates your issue? You can manually change the names/labels of computed columns when you build them in the query builder. Unfortunately, changing labels/names won't propagate through the project.

[AnnaBetts]

Hi Reeza,

Thank you for responding.

When I created the first computed column (called Provisioning) then it included the following code

(CASE

WHEN (IFC(t2.Sec_Classification_SubGroup="Securitisation - External", "Yes", "No")) = "Yes" THEN "No"

WHEN t2.Mortgage_Insurance = "Yes" THEN "No"

WHEN (ifc(t2.Account_Security_CD in ("1","2","5"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "Yes" THEN "No"

WHEN (ifc(t2.Account_Security_CD in ("1","2","5"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "No" AND (IFC (t2.MAX_of_ValuationDate > t7.Logical_Date_less_365,"Yes","No")) = "Yes" AND (ifc(t2.LVR>80,"Yes","No")) = "No" THEN "No"

WHEN (ifc(t2.Account_Security_CD in ("12"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "Yes" THEN "No"

WHEN (ifc(t2.Account_Security_CD in ("12"),"Yes","No")) = "Yes" AND (IFC (t2.MAX_of_ValuationDate > t7.Logical_Date_less_365,"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "No" AND (ifc(t2.LVR>80,"Yes","No")) = "No" THEN "No"

ELSE "Yes"

When I then want to use this Yes/No field in other calculated columns then it brings in the full code again, not the name of the computed column.  For example.. (

CASE

WHEN (CASE

WHEN (IFC(t2.Sec_Classification_SubGroup="Securitisation - External", "Yes", "No")) = "Yes" THEN "No"

WHEN t2.Mortgage_Insurance = "Yes" THEN "No"

WHEN (ifc(t2.Account_Security_CD in ("1","2","5"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "Yes" THEN "No"

WHEN (ifc(t2.Account_Security_CD in ("1","2","5"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "No" AND (IFC (t2.MAX_of_ValuationDate > t7.Logical_Date_less_365,"Yes","No")) = "Yes" AND (ifc(t2.LVR>80,"Yes","No")) = "No" THEN "No"

WHEN (ifc(t2.Account_Security_CD in ("12"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "Yes" THEN "No"

WHEN (ifc(t2.Account_Security_CD in ("12"),"Yes","No")) = "Yes" AND (IFC (t2.MAX_of_ValuationDate > t7.Logical_Date_less_365,"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "No" AND (ifc(t2.LVR>80,"Yes","No")) = "No" THEN "No"

ELSE "Yes"

END

) = "Yes" AND

t2.DTR_Level5 IN ("Mortgage Loan" , "Security Only Loan" , "Line of Credit")

AND t2.AVG_of_Current_Balance > 0

THEN t2.AVG_of_Current_Balance

ELSE 0

END

END)

My concern is if I make a change to the Provisioning column then it will not automatically flow through to the other computed columns where I referred to the Provisioning computed column.  I will need to change the code each time. 

My question was whether it was possible for Enterprise Guide to accept a reference to the column rather than bring in the full code. 

Thanks, Anna

[Reeza]

I don't know if it's possible to set EG to use the CALCULATED key word instead of bringing in the entire calculation, but in your computed column you can reference CALCULATED VARIABLE_NAME instead of dragging the computed column from the variable list. EG does seem to maintain the calculated keyword/reference in this case.

I agree not ideal though, hopefully someone else has a better answer!