Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Populate missing values in a fact table

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_idOrderDateDateStatusfinalDate
358781915432018060720180607Opened20180710
358781915432018060720180608 20180710
358781915432018060720180609 20180710
358781915432018060720180610 20180710
358781915432018060720180611 20180710
358781915432018060720180612 20180710
358781915432018060720180613 20180710
358781915432018060720180614 20180710
358781915432018060720180615 20180710
358781915432018060720180616 20180710
358781915432018060720180617 20180710
358781915432018060720180618 20180710
358781915432018060720180619 20180710
358781915432018060720180620 20180710
358781915432018060720180621On Hold20180710
358781915432018060720180622ReOpened20180710
358781915432018060720180623 20180710
358781915432018060720180624 20180710
358781915432018060720180625 20180710
358781915432018060720180626 20180710
358781915432018060720180627 20180710
358781915432018060720180628 20180710
358781915432018060720180629 20180710
358781915432018060720180630 20180710
358781915432018060720180701 20180710
358781915432018060720180702 20180710
358781915432018060720180703 20180710
358781915432018060720180704 20180710
358781915432018060720180705 20180710
358781915432018060720180706 20180710
358781915432018060720180707 20180710
358781915432018060720180708 20180710
358781915432018060720180709 20180710
358781915432018060720180710Closed20180710

 

Labels (3)
1 Solution

Accepted Solutions
Highlighted

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;

View solution in original post

1 Reply
Highlighted

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;

View solution in original post