MULTI-ROW FORMULA

[YAN1]

Hello everyone,

I am a new user for the alteryx. I have a question for the multi-row formula. 

these is raw data

tkt	first flt date	last flt date
1111111111111	20210506	EMPTY
1111111111111	EMPTY	EMPTY
1111111111111	EMPTY	EMPTY
1111111111111	EMPTY	EMPTY
1111111111111	EMPTY	20210601
2222222222222	20210503	EMPTY
2222222222222	EMPTY	EMPTY
2222222222222	EMPTY	EMPTY
2222222222222	EMPTY	EMPTY
2222222222222	EMPTY	20210530
3333333333333	20210504	EMTPY
3333333333333	EMPTY	EMTPY
3333333333333	EMPTY	EMTPY
3333333333333	EMPTY	EMTPY
3333333333333	EMPTY	EMTPY
3333333333333	EMPTY	20210521

I use multi-row formula for the first flt date as :

IF [FIRST FLT DATE]="EMPTY" THEN [Row-1:FIRST FLT DATE] ELSE [FIRST FLT DATE] ENDIF and group by tkt.

the last flt date as

:IF [LAST FLT DATE] = "EMPTY" THEN [Row+1:LAST FLT DATE] ELSE [LAST FLT DATE] ENDIF and group by tkt.

but there are some problem for the last flt date as follow: the last flt date could not change all empty to the date.

tkt	first flt date	last flt date
1111111111111	20210506	EMPTY
1111111111111	20210506	EMPTY
1111111111111	20210506	EMPTY
1111111111111	20210506	20210601
1111111111111	20210506	20210601
2222222222222	20210503	EMPTY
2222222222222	20210503	EMPTY
2222222222222	20210503	EMPTY
2222222222222	20210503	20210530
2222222222222	20210503	20210530
3333333333333	20210504	EMTPY
3333333333333	20210504	EMTPY
3333333333333	20210504	EMTPY
3333333333333	20210504	EMTPY
3333333333333	20210504	20210521
3333333333333	20210504	20210521

and I want to get the result as follow :

tkt	first flt date	last flt date
1111111111111	20210506	20210601
1111111111111	20210506	20210601
1111111111111	20210506	20210601
1111111111111	20210506	20210601
1111111111111	20210506	20210601

would you please give me some advise to solve this problem, thank you.

[atcodedog05 · Accepted solution]

Hi @YAN1 

Here is how you can do it. The best way to fill is up to down.

Workflow:

1. Using record id to set row id.

2. Using multirow formula tool groupby tkt to fill up to down for first flt date.

3. Reversing order using sort.

4.  Using multirow formula tool groupby tkt to fill up to down for last flt date.

Note: EMPTY is misspelled as EMTPY in few places 

Hope this helps : )

[YAN1]

thank you @atcodedog05