Formula for taking the final result from multiple versions.

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:

Alteryx Example.png

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!

Help

Accepted answers

kelsey_kincaid

@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

Thableaus

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,

FinalOutput.yxmd

All comments

MarqueeCrew

@Roinielmayea ,

One formula might be:

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

Cheers,

Mark

kelsey_kincaid

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