Populate missing values in a fact table

[ilanbaruch]

hi all 

in my model i have a fact table with orders and order status

order status field appear only once in the date  was modified ,

i would want to populate the missing values in order status field with the relevant status i.e bellow 

status=Open from 20180607 to 20180620

status=Reopen from 20180622 to 20180709

order_id	OrderDate	Date	Status	finalDate
35878191543	20180607	20180607	Opened	20180710
35878191543	20180607	20180608		20180710
35878191543	20180607	20180609		20180710
35878191543	20180607	20180610		20180710
35878191543	20180607	20180611		20180710
35878191543	20180607	20180612		20180710
35878191543	20180607	20180613		20180710
35878191543	20180607	20180614		20180710
35878191543	20180607	20180615		20180710
35878191543	20180607	20180616		20180710
35878191543	20180607	20180617		20180710
35878191543	20180607	20180618		20180710
35878191543	20180607	20180619		20180710
35878191543	20180607	20180620		20180710
35878191543	20180607	20180621	On Hold	20180710
35878191543	20180607	20180622	ReOpened	20180710
35878191543	20180607	20180623		20180710
35878191543	20180607	20180624		20180710
35878191543	20180607	20180625		20180710
35878191543	20180607	20180626		20180710
35878191543	20180607	20180627		20180710
35878191543	20180607	20180628		20180710
35878191543	20180607	20180629		20180710
35878191543	20180607	20180630		20180710
35878191543	20180607	20180701		20180710
35878191543	20180607	20180702		20180710
35878191543	20180607	20180703		20180710
35878191543	20180607	20180704		20180710
35878191543	20180607	20180705		20180710
35878191543	20180607	20180706		20180710
35878191543	20180607	20180707		20180710
35878191543	20180607	20180708		20180710
35878191543	20180607	20180709		20180710
35878191543	20180607	20180710	Closed	20180710

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
    order_id, OrderDate, Date, Status, finalDate
    35878191543, 20180607, 20180607, Opened, 20180710
    35878191543, 20180607, 20180608,  , 20180710
    35878191543, 20180607, 20180609,  , 20180710
    35878191543, 20180607, 20180610,  , 20180710
    35878191543, 20180607, 20180611,  , 20180710
    35878191543, 20180607, 20180612,  , 20180710
    35878191543, 20180607, 20180613,  , 20180710
    35878191543, 20180607, 20180614,  , 20180710
    35878191543, 20180607, 20180615,  , 20180710
    35878191543, 20180607, 20180616,  , 20180710
    35878191543, 20180607, 20180617,  , 20180710
    35878191543, 20180607, 20180618,  , 20180710
    35878191543, 20180607, 20180619,  , 20180710
    35878191543, 20180607, 20180620,  , 20180710
    35878191543, 20180607, 20180621, On Hold, 20180710
    35878191543, 20180607, 20180622, ReOpened, 20180710
    35878191543, 20180607, 20180623,  , 20180710
    35878191543, 20180607, 20180624,  , 20180710
    35878191543, 20180607, 20180625,  , 20180710
    35878191543, 20180607, 20180626,  , 20180710
    35878191543, 20180607, 20180627,  , 20180710
    35878191543, 20180607, 20180628,  , 20180710
    35878191543, 20180607, 20180629,  , 20180710
    35878191543, 20180607, 20180630,  , 20180710
    35878191543, 20180607, 20180701,  , 20180710
    35878191543, 20180607, 20180702,  , 20180710
    35878191543, 20180607, 20180703,  , 20180710
    35878191543, 20180607, 20180704,  , 20180710
    35878191543, 20180607, 20180705,  , 20180710
    35878191543, 20180607, 20180706,  , 20180710
    35878191543, 20180607, 20180707,  , 20180710
    35878191543, 20180607, 20180708,  , 20180710
    35878191543, 20180607, 20180709,  , 20180710
    35878191543, 20180607, 20180710, Closed, 20180710
];

FinalTable:
NoConcatenate
LOAD order_id,
	 OrderDate,
	 Date,
	 If(order_id = Previous(order_id),
	 	If(Len(Trim(Status)) = 0, Peek('Status'), Status), Status) as Status,
	 finalDate
Resident Table
Order By order_id, Date;

DROP Table Table;