Alternatives or tidier execution of IF statements

[mdw0922]

Hi

I Would like some advice being new to ETL & programming.  I want to know what function is best to use to get the following outcome for NLG on a data set.

I would like to list a set of variables and their associated values in an NLG output through the 'Formula' tool in Alteryx. Where there is no value I want to substitute the blank space for 'no record'.

Currently I use this syntax:

'"content": "' + ' [Oil Production: ' + ToString([PROD_OIL_VOL]) + 'm3 ]'
+ ' [Water Production: ' + ToString([PROD_WAT_VOL]) + 'm3 ]' + ' [Gas Production: ' + ToString([PROD_GAS_VOL]) + 'm3]' + ' [The Wellhead pressure: ' + ToString([WH_PRESS]) + 'bar ]' + '",'

Which gives me: [Oil Production: 'xyz'm3 ] . [Gas Production: 'xyz'm3 ] . [Water Production: 'xyz'm3 ] [WHP Pressure: 'xyz'bar ]

If i do not have a value in my dataset I get e.g [Oil Production: m3 ] - which looks nasty, I'd like to be able to substitute  "'xyz'm3" for "no record" - is this possible in this way?

I appreciate any help or guidance, am new to this so bear with me.

Thanks

[DavidThorpe]

Hi @mdw0922 

I've taken a look at replicating this and would recommend a series of pivots to prepare the data. This splits each value to tag it's component parts (new field name and units), builds the string per field then concatenates by recordID.

You can see this now dynamically handles 'no record' status in your raw data:

I've attached the workflow for you to test out. 

If this has resolved your problem, please mark it as solved - otherwise please let me know!

Thanks

David

[mdw0922]

Thanks David. much appreciated, I'll get back to you ASAP

[JWS · Accepted solution]

@mdw0922 here is a solution that takes a more dynamic approach using if statements. 

IIF(isNull([PROD_OIL_VOL]),' [Oil Production: ' + "no record" +']',' [Oil Production: ' + ToString([PROD_OIL_VOL]) + 'm3 ]') +
IIF(isNull([PROD_WAT_VOL]),' [Water Production: ' + "no record" +']',' [Water Production: ' + ToString([PROD_WAT_VOL]) + 'm3 ]') +
IIF(isNull([PROD_GAS_VOL]),' [Gas Production: ' + "no record" +']',' [Gas Production: ' + ToString([PROD_GAS_VOL]) + 'm3]') +
IIF(isNull([WH_PRESS]),' [The Wellhead pressure: ' + "no record" +']',' [The Wellhead pressure: ' + ToString([WH_PRESS]) + 'bar ]')

[mdw0922 · Accepted solution]

Thanks thjis did just the job, and removed units hwen 'no record' was input.  Much appreciated