Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;