Hello,
I want to create a calculated column in the transformation tool of TDP where I check if a value is {null} (or not {null}) and return something accordingly. I cannot figure out how TDP represents the null value when creating a calculated column. Appreciate any help.
Thanks,
Troy
instead of calculated field try using Find and Replace. That rule is set up to recognize Nulls and allow you to set value.
What I have is one column with dates/nulls and another column with a status of that project. I would like to create a column which checks if I have a date in the one column and a status of "On Air" in the other column. I don't want to replace the null values in this case. Is there a way to check a value is null in an if statement?
Let me play around with it and let you know.
Please try Group Column if you aim to check a column value is null or not null and return something, not create calculated column.
I have the same question. Besides using an Nvl command to do a null check is there a specific format to see if a column is equal/not equal to null? Example if I wanted to say
If([Test_Column] = null, 'Null Value', 'Not a Null Value')
How would be represent the null in the comparison statement to have it check? I could always write
If(Nvl([Test_Column],0) = 0, 'Null Value', 'Not a Null Value')
but if I do that then I have to make sure that the replacement value for the null matches the column data type or it will generate an error.
Any info is appreciated.
My understanding is you want to check null value for one column, if it's null keep null value, if not equal null replace with other value, please try Group Column, is this you are looking for?
The Grouping makes sense when just looking at transforming the values of a single column, but I think the question here is how can we in building a formula evaluate a column to see if a value is null. For example the formula below is checking a column to see if a value is null, if it is not a calculation takes place, if it is null text is entered in the new calculated field.
If([TestColumn] != {null}, Sum([Sales]+[SalesTax]), 'No Sale')
As of right now the only way I can write this formula to work as intended is to do it as shown below
If(Nvl([TestColumn],0) != 0, Sum([Sales]+[SalesTax]), 'No Sale')
I know the Nvl function can do the check for the null in this calculation, but is there a specific syntax to be used to make the first formula work? If not, working forward with the Nvl is acceptable. It would just help to streamline the formulas in a Nested If scenario.
Thanks
The only way I know how to make a calculated value based on another field is to use a case statement in the SQL. See Query Builder example below for adding Case statement. Here I am checking for Null in Amount_Billed and created a new column and setting value to 0.0 if null or original value if not null.
I think this would work. Can I created a calculated field in the query builder between two different tables? I have one field that shows a project status and another field that shows the date that project was completed. These columns are from two different tables. I want to create a third column which says "TRUE" if there is a date and the status shows Complete but "FALSE" if there is a date but the status does not show Complete.
