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