2 Replies Latest reply on Aug 24, 2016 3:59 AM by J CK

# How to combine multiple column based on null and create a single column

Hello Data people!!!

I am breaking my head to find a solution the below mentioned challenge, i have a set of data as mentioned below.

These are the life stages of a particular item, I want to do a calculation which tells me, which stage my item is based on the date of stage finished.

For eg: my item no# 1 is in Stage 2, based on the finished date of Stage1.

I have my target or future column in green on the right.

Also attached a spreadsheet of the same content.

Any help is appreciated   Thank you!!!!!!

 Item Date_Stage1_finished Date_Stage2_finished Date_Stage3_finished Date_Stage4_finished Date_Stage5_finished Date_Stage6_finished Date_Stage7_finished Current Stage- My target 1 7/25/2016 Stage 2 2 7/25/2016 Stage 2 3 7/25/2016 Stage 2 4 7/25/2016 Stage 4 5 7/25/2016 7/25/2016 7/29/2016 Stage 4 6 7/25/2016 7/25/2016 7/28/2016 Stage 4 7 7/25/2016 7/25/2016 7/29/2016 Stage 4 8 7/25/2016 7/25/2016 8/10/2016 Stage 4 9 7/25/2016 7/26/2016 Stage 3 10 7/25/2016 8/10/2016 Stage 3 11 7/25/2016 8/10/2016 Stage 3 12 7/27/2016 7/29/2016 Stage 3 13 7/27/2016 7/29/2016 7/29/2016 7/29/2016 Stage 5 14 7/28/2016 7/28/2016 8/10/2016 8/10/2016 Stage 5
• ###### 1. Re: How to combine multiple column based on null and create a single column

Well.. I only have this:

IF     NOT ISNULL([Date Stage7 finished]) THEN "Stage 7"
ELSEIF NOT ISNULL([Date Stage6 finished]) THEN "Stage 6"
ELSEIF NOT ISNULL([Date Stage5 finished]) THEN "Stage 5"
ELSEIF NOT ISNULL([Date Stage4 finished]) THEN "Stage 4"
ELSEIF NOT ISNULL([Date Stage3 finished]) THEN "Stage 3"
ELSEIF NOT ISNULL([Date Stage2 finished]) THEN "Stage 2"
ELSEIF NOT ISNULL([Date Stage1 finished]) THEN "Stage 1"
ELSE "Something is wrong!"
END

This might be off by 1 stage but you should get the idea.

• ###### 2. Re: How to combine multiple column based on null and create a single column

Thanks a lot Dmitry!!!! Cheers!!