I don't understand the logic of your result table. But to match the Date from the Sales table with the interval in the Price_List table you need to use the Intervalmatch function. Try the script below and then add two table boxes for the two tables that are created in the script.
Note, the INLINE loads are just examples. You can use your excel file as a source instead.
Price_List:
LOAD
Item,
Item_code,
price,
Date(Date#(left(price.start,10),'YYYY-MM-DD'),'YYYY-MM-DD') as price.start,
Date(Alt(Date#(price.exp,'YYYY-MM-DD'),makedate(2099)),'YYYY-MM-DD') as price.exp
INLINE [
Item, Item_code, price, price.start, price.exp
ITEM_NAME_1, 0102213, "3,07", 2010-04-12 00:00:00.000, 2014-04-01
ITEM_NAME_2, 0151003, "7,47", 2010-04-12 00:00:00.000, 2014-12-01
ITEM_NAME_3, 0105003, "3,97", 2014-04-12 00:00:00.000, 2016-01-01
ITEM_NAME_4, 0715206, "3,47", 2010-04-12 00:00:00.000, 2014-05-31
ITEM_NAME_5, 0602006, "3,18", 2013-04-12 00:00:00.000,
ITEM_NAME_2, 0151003, "9,99", 2014-12-02 00:00:00.000,
ITEM_NAME_4, 0715206, "4,47", 2014-06-01 00:00:00.000,
];
Sales:
LOAD
ID,
Item_code,
Client_ID,
Date(Date#(Date,'YYYY-MM-DD'),'YYYY-MM-DD') as Date,
kg,
price as sales_price
INLINE [
ID, Item_code, Client_ID, Date, kg, price
18912, 0102213, DAR002, 2014-01-02, "12,000", "1,45"
18913, 1115102, IND015, 2015-01-02, "10,000", "2,29"
18914, 0105003, DAI00012, 2015-05-02, "2,6", "1,56"
18915, 0715206, DAI00007, 2015-01-02, "1309,000", "1,1"
18916, 0602006, DAI00012, 2016-03-02, "0,885", "4,08"
18917, 0151003, DAI00012, 2015-01-02, "1342,000", "1,35"
];
LEFT JOIN (Sales)
IntervalMatch(Date,Item_code)
LOAD
price.start,
price.exp,
Item_code
RESIDENT
Price_List
;
LEFT JOIN (Sales)
LOAD
price.start,
price.exp,
Item_code,
price as pricelist_price
RESIDENT
Price_List
;
DROP FIELDS price.start, price.exp FROM Sales;
talk is cheap, supply exceeds demand
