Dynamic Price based on Period

[rammuthiah]

Hi,

I have part number and dates, for a particular period the value is fixed, after exceeding the period it should show the varible price

For instance,

Part Number	From	To	Value
A	Jan 2022	Mar 2022	500
A	Apr 2022	Jun 2022	550
A	Jul 2022	Sep 2022	580
A	Oct 2022	Mar 2023	650

Need O/P like below

A	Jan	2022	500
A	Feb	2022	500
A	Mar	2022	500
A	Apr	2022	550
...	..
A	Oct	2022	650
A	Mar	2023	650

...

[Mark_Little]

HI @rammuthiah 

we do a similar thing, How i have addressed it is by making a Key of Part No, Month Year. On both the Pricing table and Fact table. i did create the missing months, but you could do a join where you have monthly price and applymap the standard price where the value is null.

[rammuthiah]

Here i do have missing month information. could you explain in detail with the logic/script?

[RsQK]

Hi, maybe something like this:

data:
LOAD
"Part Number",
DATE(DATE#(From,'MMM YYYY'))	AS From,
DATE(DATE#(To,'MMM YYYY'))		AS To,
Value
INLINE [
Part Number|From|To|Value
A|Jan 2022|Mar 2022|500
A|Apr 2022|Jun 2022|550
A|Jul 2022|Sep 2022|580
A|Oct 2022|Mar 2023|650
] (DELIMITER IS '|');

temp_min_max:
LOAD
MIN(From) AS min_date,
MAX(To)	  AS max_date
RESIDENT data;

temp_dates:
LOAD
ADDMONTHS(min_date,ITERNO()-1) AS date
RESIDENT temp_min_max
WHILE ADDMONTHS(min_date,ITERNO()-1) <= max_date;

DROP TABLE temp_min_max;

INTERVALMATCH(date)
LEFT JOIN (temp_dates)
LOAD DISTINCT
From,
To
RESIDENT data;

LEFT JOIN (data)
LOAD
From,
To,
date
RESIDENT temp_dates;

DROP TABLE temp_dates;

result:
LOAD
"Part Number",
MONTH(date) as Month,
YEAR(date)	as Year,
Value
RESIDENT data;

DROP TABLE data;

[MarcoWedel]

one solution might be:

table1:
LOAD *,
     Month(Date) as Month,
     Year(Date) as Year;
LOAD *,
     MonthName(From,IterNo()-1) as Date
While MonthName(From,IterNo()-1) <= To;
LOAD PartNumber,
     Date#(From,'MMM YYYY') as From,
     Date#(To,'MMM YYYY') as To,
     Value
Inline [
    PartNumber, From, To, Value
    A, Jan 2022, Mar 2022, 500
    A, Apr 2022, Jun 2022, 550
    A, Jul 2022, Sep 2022, 580
    A, Oct 2022, Mar 2023, 650
];