6 Replies Latest reply: Jan 9, 2013 1:13 AM by Manish Kukreti RSS

    How to pick latest price?

      Hi

       

      How can I add additional column into my script that would show the latest price of the product? I have columns for Product ID, Price and Valid from, and I would like to create additional column that would show the latest price. Please see the situation below.

       

      Product IDPriceValid fromLatest price
      100012,001/01/20081,8
      100021,001/02/20081,4
      100021,201/03/20081,4
      100033,001/04/20083,5
      100011,901/04/20081,8
      100021,401/05/20081,4
      100011,801/06/20081,8
      100033,501/06/20083,5

       

      What I am doing is that I try to build sales forecast. I have sales forecast volume and then I have price list. However in many cases price has been changed once or often and I would like to use always the latest price in my forecast.

       

      I was thinking to do this in script that would that make more sense to do it in expression?

       

      Thanks,

       

      Janne

        • Re: How to pick latest price?
          christian juillard

          Hi Janne,

           

          either you load only the price from max(date), or if you need all the product history, you load a second table with

          product_id, lastprice

          This will jopin tables on product_id and you will get LastPrice in every cell for each product.

          Regards

          Christian

          • Re: How to pick latest price?
            Gysbert Wassenaar

            Try this expression: FirstSortedValue(total <[Product ID]> Price,-[Valid from]). Or use it with a group by clause in the script. See attached qvw.

              • Re: How to pick latest price?

                Hi Gysbert,

                 

                Looks like that your clause in the script seems to work in most of the cases. However, in case that there is only on price in the price table it doesn't anything. Did I do something wrong? I just copied your script

                  • Re: How to pick latest price?
                    Gysbert Wassenaar

                    No, the table box won't show duplicate records. Are your other fields company code and customer in the same table? The group by function doesn't include them so there could be duplicates if only product id is considered but the real unique combination is for example product id + company code.

                    Try for example this script:

                    T3:
                    LOAD *
                    INLINE [
                        Product ID2, Price2, Valid from2, Customer
                        10001, 2, 01-01-2008,1
                        10002, 2, 01-02-2008,1
                        10002, 2, 01-03-2008,1
                        10002, 2, 01-03-2008,2   
                        10003, 3, 01-04-2008,1
                        10001, 1.9, 01-04-2008,1
                        10001, 1.8, 01-06-2008,1
                        10003, 3.5, 01-06-2008,1
                        10004, 4.1, 01-03-2008,1   
                    ];

                    T4:
                    LOAD [Product ID2], FirstSortedValue(Price2, -[Valid from2]) as LP2
                    Resident T3
                    group by [Product ID2];

                     

                    You won't get a value LP2 for product id 10002, because there are two records that are not unique if you disregard Customer. If you include Customer in the group by clause then you will get a value back.

                      • Re: How to pick latest price?

                        Thanks,

                         

                        Looks like you are right, there seems to be duplicates and that is probably because I have joined two tables there together. The unique combination includes all fields company code, customer and product. Would you have some easy solution to get rid of these duplicates? I already tried to add clause "where LP > 0" into the resident load, but it didn't worked.

                         

                        Regards

                        Janne

                  • Re: How to pick latest price?

                    Hi Jaane

                     

                    Hope attached QVW will help ......