Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Alternatives or tidier execution of IF statements

mdw0922
5 - Atom

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

4 REPLIES 4
DavidThorpe
Alteryx
Alteryx

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:

 

DavidThorpe_0-1575567458342.png

DavidThorpe_1-1575567484222.png

 

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
5 - Atom

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

JWS
Alteryx Alumni (Retired)

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

 

JWS_0-1575570404662.png

 

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
5 - Atom

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

Labels