Populating a new field from 4 cells in a priority order

[MCicc]

Hey all.

This is my first time posting on the Alteryx boards so pardon me if this is the wrong area to be posting this.

I'm working to develop a workflow that populates a field based off of the content of 4 other fields in a specific priority order.

Context: I have 4 metadata fields (DateCreated, DateLastMod, DateRcvd and DateSent) and I need them to populate the LeadDate field in a specific order.

I need the following to happen.

1. Create a LeadDate field

2. Where LeadDate is blank, LeadDate = DateSent

3. Where LeadDate is blank, LeadDate = DateRcvd

4. Where LeadDate is blank, LeadDate = DateLastMod

5. Where LeadDate is blank, LeadDate = DateCreated

6. Where LeadDate is blank, LeadDate = "00/00/0000"

I just started really using Alteryx so don't destroy me on my approach. I'm sure I'm doing this wrong but gotta start somewhere. I created the LeadDate field and made it equal to DateSent in the first Formula. In the second Formula I have the following:

IF IsEmpty([LeadDate]) THEN [DateRcvd]
ELSEIF IsEmpty([LeadDate]) THEN [DateLastMod]
ELSEIF IsEmpty([LeadDate]) THEN [DateCreated]
ELSE "00/00/0000"
ENDIF

What am I doing wrong here?

Thanks in advance,

Mike

[MarqueeCrew · Accepted solution]

hi @MCicc ,

 here is my take:

if you have a lead date, keep it otherwise look for another date. 

IF !IsEmpty([LeadDate]) THEN [LeadDate] ELSEIF

!IsEmpty([DateSent]) THEN [DateSent] ELSEIF

!IsEmpty([DateRcvd]) THEN [DateRcvd] ELSEIF !IsEmpty([DateLastMod]) THEN [DateLastMod] ELSEIF !IsEmpty([DateCreated]) THEN [DateCreated]
ELSE

"00/00/0000"

EndIf

mike, this field must be string. 

cheers,

 mark

[MCicc]

Mark,

That did the trick! 

There's one final part of this (I think) that I can't solve.

Now that I've populated the leaddate for all top-level (parent) records, I need that date to be propagated to the children records (where the date is null).

In my above example, T00000080 is the parent record and the records T00000081 & T00000082 are considered the children. The children need the LeadDate propagated down to them. 

I've identified the Null records (children) but I'm not sure how to reference a record from a relational field (Group Identifier) and grab the date from that record.

If someone could provide some insight, that'd be appreciated.

Thanks,

Mike

[deviseetharaman · Accepted solution]

Hi @MCicc 

You can use the Multi Row Formula tool here. Choose Update Existing Field and Select LeadDate. The Expression can be:

if isnull(LeadDate) and [Group Identifier] = [Row-1: Group Identifier] then [Row-1: LeadDate] else [Lead Date] endif

[MCicc]

That did the trick.

Thanks for the help!

Much appreciated,

Mike