Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
...
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.
Here i do have missing month information. could you explain in detail with the logic/script?
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;
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
];