Formula for taking the final result from multiple versions.

[Roinielmayea]

Hi,

New to Alteryx and have been trying to create a Final Output column that pulls an amount from the most recent version table. I've already used the JOIN tool to combine the files but would like to have one final column, "Final Output Result" to capture only the most recent version. (So if row 2 has two amounts I would like for it to pick up the most recent version amount)

See below:

The files do contain NULL values which are not the same as 0 amounts.

I've tried multiple versions of IF ELSE THEN formulas with ISNULL([Version 4]) THEN [Version 3]) but that doesn't seem to work.

Any help would be much appreciated!

Thanks!

[MarqueeCrew]

@Roinielmayea ,

One formula might be:

Max([Version 1],[Version 2],[Version 3],[Version 4])

Cheers,

Mark 

[kelsey_kincaid · Accepted solution]

@Roinielmayea Does your data contain Null values or Empty values? I think the below formula should accomplish what you need. If you have empty values rather than nulls then you would just need to change the !IsNull statements to !IsEmpty

IF !IsNull([Version 4]) THEN [Version 4] ELSEIF
!IsNull([Version 3]) THEN [Version 3] ELSEIF
!IsNull([Version 2]) THEN [Version 2] ELSEIF
!IsNull([Version 1]) THEN [Version 1] ELSE
[Version 0] ENDIF

[Roinielmayea]

Thanks for the response Mark! The amounts could decrease so in the newer versions so I wouldn't be able to do MAX but thanks for the suggestion!

[Roinielmayea]

This worked! Thanks so much!

[Thableaus · Accepted solution]

Hi @Roinielmayea 

If you have too many version fields I'd strongly suggest that you transpose all these version fields.

Then, clean up the Empty Values and use summarize tool to get the last version.

Join everything back to your dataset and you will have a dynamic solution.

WF attached.

Cheers,