How to replace null values with a look up value?

[that_anonymous_guy]

 Hi everyone!

This is a test data that I have. I have pasted the entire script below:

test2:

LOAD
*
INLINE [

Store,Activity Code, Date, Target, Duration, progress

A, 001, 3/1/2019,100,10,10
A, 002, 3/2/2019,100,10,10
A, 003, 3/5/2019,100,10,10
A, 005, 3/7/2019,100,10,10
A, 008, 3/13/2019,100,10,10
B, 012, 3/7/2019,100,5,20
B, 013, 3/9/2019,100,7,20
B, 016, 3/12/2019,100,3,20
];

Join(test2)
Load
Store,
Date(MinDate+IterNo()-1) as Date
while MinDate+IterNo()-1<= MaxDate;

Load
Store,
Date('2/1/2019') as MinDate,
Max (Date ) as MaxDate
resident test2 group by Store;

Final:
NoConcatenate
Load
Store,
Date,
if(IsNull([Activity Code]),Peek([Activity Code]),[Activity Code]) as [Activity Code],
if(IsNull(Target),Peek(Target),Target) as Target,
if(IsNull(Duration),Peek(Duration),Duration) as Duration,
if(IsNull(progress),Peek(progress),progress) as progress

Resident test2
order by Store,Date;
Drop Table test2;

I am adding missing rows to my inline table(basically dates). I am grouping the dates on the basis of stores. For dates earlier than the first date used in inline load(for every store),  I am getting null value in peek section .For store A, I'm getting null values(probably because there's nothing to peek from as it is the first row in the table) and for store B I'm getting last data of Store A in place of null till the row(from inline table) arrives. How can I get the first data of store A for every rows before 3/1/2019(first entry for store A) and first value of store B for every row before 3/7/2019(first entry for store B)?

[Chanty4u]

Try this code 

// Load the data

test2:

LOAD

    *

INLINE [

    Store,Activity Code,Date,Target,Duration,progress

    A,001,3/1/2019,100,10,10

    A,002,3/2/2019,100,10,10

    A,003,3/5/2019,100,10,10

    A,005,3/7/2019,100,10,10

    A,008,3/13/2019,100,10,10

    B,012,3/7/2019,100,5,20

    B,013,3/9/2019,100,7,20

    B,016,3/12/2019,100,3,20

];

// Add missing dates

Join(test2)

Load

    Store,

    Date(MinDate+IterNo()-1) as Date

while MinDate+IterNo()-1 <= MaxDate;

// Get the minimum and maximum dates for each store

Load

    Store,

    Min(Date) as MinDate,

    Max(Date) as MaxDate

Resident test2

Group By Store;

// Fill in missing values

Final:

NoConcatenate

Load

    Store,

    Date,

    If(IsNull([Activity Code]), Peek([Activity Code]), [Activity Code]) as [Activity Code],

    If(IsNull(Target), Peek(Target), Target) as Target,

    If(IsNull(Duration), Peek(Duration), Duration) as Duration,

    If(IsNull(progress), Peek(progress), progress) as progress

Resident test2

Order By Store, Date;

// Replace null values for Store A with the first value of Store A

For i = 1 to FieldValueCount('Store')

    Let vStore = FieldValue('Store', i);

    If(vStore = 'A') Then

        Final:

        NoConcatenate

        Load

            Store,

            Date,

            If(IsNull([Activity Code]) and Date < Date#('3/1/2019', 'M/D/YYYY'), Peek([Activity Code]), [Activity Code]) as [Activity Code],

            If(IsNull(Target) and Date < Date#('3/1/2019', 'M/D/YYYY'), Peek(Target), Target) as Target,

            If(IsNull(Duration) and Date < Date#('3/1/2019', 'M/D/YYYY'), Peek(Duration), Duration) as Duration,

            If(IsNull(progress) and Date < Date#('3/1/2019', 'M/D/YYYY'), Peek(progress), progress) as progress

        Where Store = '$(vStore)';

    EndIf

Next i;

// Replace null values for Store B with the first value of Store B

For i = 1 to FieldValueCount('Store')

    Let vStore = FieldValue('Store', i);

    If(vStore = 'B') Then

        Final:

        NoConcatenate

        Load

            Store,

            Date,

            If(IsNull([Activity Code]) and Date < Date#('3/7/2019', 'M/D/YYYY'), Peek([Activity Code]), [Activity Code]) as [Activity Code],

            If(IsNull(Target) and Date < Date#('3/7/2019', 'M/D/YYYY'), Peek(Target), Target) as Target,