Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a list of many products joined with combination of all dates. Some Products change value over the time so i need to assign
a valid value in exact time.. Any ideas how i can handle that ?
Product | Month | Value | NewField |
A | 201708 | 15 | |
A | 201709 | 15 | |
A | 201710 | 15 | 15 |
A | 201712 | 15 | |
A | 201801 | 15 | |
A | 201802 | 15 | |
A | 201803 | 15 | |
B | 201708 | 20 | |
B | 201709 | 20 | 20 |
B | 201710 | 20 | |
B | 201712 | 20 | |
B | 201801 | 30 | 30 |
B | 201802 | 30 | |
B | 201803 | 30 | |
C | 201708 | 60 | 60 |
C | 201709 | 60 | |
C | 201710 | 60 | |
C | 201712 | 60 | |
C | 201801 | 60 | |
C | 201802 | 80 | 80 |
C | 201803 | 80 |
Hi,
Could you please elaborate more to understand the requirement.
Cheers
Paridhi
I understand the forward filling of data... but backward filling?
Try this
Table:
LOAD * INLINE [
Product, Month, Value
A, 201708,
A, 201709,
A, 201710, 15
A, 201712,
A, 201801,
A, 201802,
A, 201803,
B, 201708,
B, 201709, 20
B, 201710,
B, 201712,
B, 201801, 30
B, 201802,
B, 201803,
C, 201708, 60
C, 201709,
C, 201710,
C, 201712,
C, 201801,
C, 201802, 80
C, 201803
];
Table1:
LOAD *,
If(Product = Previous(Product) and Len(Trim(Value)) = 0, Peek('NewFieldTemp'), Value) as NewFieldTemp
Resident Table
Order By Product, Month;
DROP Table Table;
FinalTable:
LOAD *,
If(Product = Previous(Product) and Len(Trim(NewFieldTemp)) = 0, Peek('NewField'), NewFieldTemp) as NewField
Resident Table1
Order By Product, Month desc;
DROP Table Table1;
Sure, My table has 3 fields , Product, YearMonth and Value valid for Month.
In case abouve
Product A has one value in 201710 so i would like to populate this value to other cells. Until today Year Month
Product B has two values 201709 and 201801 i would like to assign value from 201709 to all months before 201709 and before 201801. Value from 201801 after this month until new value appear.
Product C has two values first one 201808 will be valid until 201801, 201802 has new value and should be populate until 201803.
The result i'm looking for presented below.
This is what i was looking for !
Great
May be like below
T1:
Load * Inline
[
Product,Month,Value,NewField
A,201708,,15
A,201709,,15
A,201710,15,15
A,201712,,15
A,201801,,15
A,201802,,15
A,201803,,15
B,201708,,20
B,201709,20,20
B,201710,,20
B,201712,,20
B,201801,30,30
B,201802,,30
B,201803,,30
C,201708,60,60
C,201709,,60
C,201710,,60
C,201712,,60
C,201801,,60
C,201802,80,80
C,201803,,80
];
T2:
NoConcatenate Load
If((Product=Peek(Product) AND Len(Trim(Value))) OR (Product<>Peek(Product) AND Len(Trim(Value))),Value,Peek(x)) As x,
Product,Month,Value,NewField
Resident T1
Order By Product, Month
;
Drop Table T1;
T3:
NoConcatenate Load
If(Len(Trim(Peek(x)))=0 and Len(Trim(x))=0,Peek(y),if(len(trim(x))=0,Peek(x),x)) as y,
Product,Month,Value,NewField,x
Resident T2
Order By Product, Month desc
;
Drop Table T2;
Check out this post.
With Rob Wunderlichs approach you can work with high and low levels.
Helped me a lot to generate age categoeries.
creating an age group from a date field
BR