4 Replies Latest reply: Apr 21, 2016 2:17 PM by Pulkit Thukral RSS

    Price record between two dates

    Aurimas Driskius

      Hi All,

       

      What i am trying to do is get a price for a record where the SALES_TABLE  "Date" is between two dates ( price.start , price.exp) and where the "Item_code" and Customer Name from SALES_TABLE is DAI000**

       

      Screenshot_1.png

        • Re: Price record between two dates
          Sunny Talwar

          Would you be able to provide the above screenshot data in an Excel file?

            • Re: Price record between two dates
              Aurimas Driskius

              Price_list

               

                   

              ItemItem_codepriceprice.startprice.exp
              ITEM_NAME_101022133,072010-04-12 00:00:00.0002014-04-01
              ITEM_NAME_201510037,472010-04-12 00:00:00.0002014-12-01
              ITEM_NAME_301050033,972014-04-12 00:00:00.0002016-01-01
              ITEM_NAME_407152063,472010-04-12 00:00:00.0002014-05-31
              ITEM_NAME_506020063,182013-04-12 00:00:00.000
              ITEM_NAME_201510039,992014-12-02 00:00:00.000
              ITEM_NAME_407152064,47

              2014-06-01 00:00:00.000

               

               

               

              Sales

               

                    

              IDItem_codeClient_IDDatekgprice
              189120102213DAR0022014-01-0212,0001,45
              189131115102IND0152015-01-0210,0002,29
              189140105003DAI000122015-05-022,61,56
              189150715206DAI000072015-01-021309,0001,1
              189160602006DAI000122016-03-020,8854,08
              189170151003DAI000122015-01-021342,0001,35
                • Re: Price record between two dates
                  Gysbert Wassenaar

                  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;

              • Re: Price record between two dates
                Pulkit Thukral

                Somewhat like :

                •  

                  only({<ClientID={'DAI000*'},Date={">=$(=date(floor(monthstart(addmonths(today()-14))),'DD-MM-YYYY'))"}*{'<$(=date(floor(monthstart(addmonths(today()-8))),'DD-MM-YYYY'))"} >}price)