Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a price table with 4 columns:
Product Start_Date Final_Date Price
P1 2016-01-01 2017-12-31 20
P1 2018-01-01 2019-12-31 25
P2 2016-01-01 2017-12-31 32
P2 2018-01-01 2019-12-31 35
The report sales should be, example:
%Date Product QTY Price
2018-05-01 P1 2 25
2019-01-03 P2 4 35
But the Column Price is null when i make this Set Analysis:
Max({$<Year=,Month=,%Date={'>=Start_Date<=Final_Date'}>}Price)
I tried, this one, with variables vDateSt=Start_Date and vDateFin=Final_Date
max({$<Año=,Mes=,%FECHA={'>=$(=vDateSt)<=$(=vDateFin)'}>}Price)
But this doesn't work anyway
Could someone help me please.
You need an interval match to fetch the correct prices:
Left Join(Fact)
IntervalMatch(Start_Date, Final_Date, Product)
LOAD Start_Date, Final_Date, Product
Resident PriceTable;
//optional ----
Left Join(Fact)
LOAD *
Resident PriceTable;
DROP Table PriceTable;
Now use the Price field.
These second join and drop are to simplify the resulting data model, but are not strictly necessary.
Sorry, i got it, i know what is wrong.
This is the answer, thanks Jontydkpi
You need an interval match to fetch the correct prices:
Left Join(Fact)
IntervalMatch(Start_Date, Final_Date, Product)
LOAD Start_Date, Final_Date, Product
Resident PriceTable;
//optional ----
Left Join(Fact)
LOAD *
Resident PriceTable;
DROP Table PriceTable;
Now use the Price field.
These second join and drop are to simplify the resulting data model, but are not strictly necessary.
Hello Jonty,
i have tried your solution, but this doesn't work.
This is an extract from the model, could you tell me what am i doing wrong?
Sorry, i got it, i know what is wrong.
This is the answer, thanks Jontydkpi