Propagate Above Product and/or Generate Missing Data

[Anonymous]

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.

[Anonymous]

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;