Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have an application that it show Product values by Year by Quarter.
But I would like to manage the following rules in my application:
- IF a product is shown in Q1 but not in Q2, I'd like to propagate the above product line. (P2)
- IF a product value is NULL, I'd like to generate a missing data with above value of this product. (P3)
- IF a product is added in the first Quarter of a year only, propagate this product and values until end of year. (P4)
Data:
LOAD * INLINE [
Year, Quarter, Product, Value
2017, Q1, P1, 1
2017, Q1, P2, 2
2017, Q1, P3, 3
2017, Q1, P4, 4
2017, Q2, P1, 11
2017, Q2, P2, 22
2017, Q2, P3, 33
2017, Q3, P1, 111
2017, Q3, P2, 222
2017, Q3, P3, 333
2017, Q4, P1, 1111
2017, Q4, P2, 2222
2017, Q4, P3, 3333
2018, Q1, P1, 10
2018, Q1, P2, 5
2018, Q1, P3, 1
2018, Q2, P1, 12
2018, Q2, P3,
];
Could you help me please ?
Thank in advance.
J.
I have done the following script :
My first three point are OK.
But just my last point is not completely done. P4 is not propagate until end of year.
I think the problem is the definition of my variable vMaxQuarter accord to a Year.
Any Idea ?
See below :
// A - Load all existing Product Values, By Year and by Quarter
TempProductValue:
LOAD
Product,
Year,
Dual('Q' & right(Quarter, 1) , right(Quarter, 1)) as Quarter,
Value,
Product & '|' & Year &'-'& Quarter as Product_By_Date
RESIDENT Data;
// B - Generate all combinations of Product, Year and Quarter
//////////////////////////////////////////////////////////////////////////////////////////////
LET vMinQuarter = 1;
LET vMaxQuarter = right(peek('Quarter'), 1);
TRACE MIN : $(vMinQuarter);
TRACE MAX : $(vMaxQuarter);
TempProduct_x_Year_Quarter:
LOAD
DISTINCT
Product, Year
RESIDENT TempProductValue;
JOIN
LOAD
RecNo() + $(vMinQuarter) as Quarter
AUTOGENERATE $(vMaxQuarter) - $(vMinQuarter)
;
// C - Append Missing records onto the Product Value Table
Concatenate(TempProductValue)
LOAD * WHERE NOT EXISTS(Product_By_Date);
LOAD
Product,
Year,
Quarter,
Product & '|' & Year &'-'& Quarter as Product_By_Date
RESIDENT TempProduct_x_Year_Quarter;
;
// D - Final Table with propagation
Product:
NoConcatenate
LOAD
Product,
Year,
Quarter,
if(Product = peek(Product) AND Year = Peek(Year) AND Len(Trim(Value)) = 0
, peek(Value)
, Value
) as Value
RESIDENT TempProductValue
ORDER BY Product, Year, Quarter
;
DROP TABLE TempProductValue;
DROP TABLE TempProduct_x_Year_Quarter;
DROP TABLE Data;