3 Replies Latest reply: Aug 23, 2016 7:13 AM by philippe regnier RSS

    selecting an expression when several are true

    philippe regnier

      Good afternoon,

       

      I have 2 tables one with orders and the other where I record the type of payment method fees (paypal, visa...).

      When I check an order, it has a dimension (Method) for the payment so I can directly pick the associated fees in the other table.


      My problem is that for certain payment methods fees are changing at certain dates. For instance,


      Method / StartDate / Fee

      paypal / 2008-2-3 / 0,5

      visa / 2004-6-3 / 0,4

      visa / 2010-12-9 / 0,3

       

      In the above example, I need to select the fee value for which the order date match with the StartDate for which the payment fee changed. If an order using visa payment is from 2008 then the payment fee must be 0,4 but if the order is from 2012 then the payment fee should be 0,3.


      So far, since my order table and payment fee table are connected with the Method dimension, the Fee returns no value as there are 2 valid choice. I wonder how to approach this, I know I need to compare the order date with the payment fee StartDate but I haven't figured out how.


      Thanks in advance for your help.

       

      philippe

       





        • Re: selecting an expression when several are true
          Charlie Hudson

          Hi Philippe,

           

          The IntervalMatch function is likely the answer you need here, please check this previous post:

           

          IntervalMatch function

           

          All the best,

           

          Charlie

          • Re: selecting an expression when several are true
            Liron Baram

            hi

            you can do it in several ways :

            1. create a record for each payment method for each date
                you can create it with this load script

            Payments:

            load *,

                  date(if(Previous(Method)<>Method,Today(),Previous(StartDate)-1 )) As EndDate

            Resident PaymentsTemp

            order by Method,StartDate DESc;

            drop table PaymentsTemp;

             

            PaymentsList:

            load *,

                 date(StartDate+IterNo()-1) As DateList

            Resident Payments

            while date(StartDate+IterNo()-1) <= EndDate;

             

            drop table Payments;

              

            this script will create a table you can connect to the orders

             

            the other method is use interval match

            in that case you'll script will be something like

             

            Payments:

            load *,

                  date(if(Previous(Method)<>Method,Today(),Previous(StartDate)-1 )) As EndDate

            Resident PaymentsTemp

            order by Method,StartDate DESc;

            drop table PaymentsTemp;

             

            Inner Join

            IntervalMatch ( OrderDate, Method)

            LOAD StartDate, EndDate, Method

            Resident Payments;

            • Re: selecting an expression when several are true
              philippe regnier

              Dear all,

               

              Thanks for your answers.

              As a newbie still to date, I must say your solutions involving scripting scared me a bit.

              It does seem to make sense though.

               

              I did find an alternative solution while working in parallel so I don't know if I'll dive in your suggestions just yet.

               

              What I have been using is the firstsortedvalue function:

               

              FirstSortedValue(

                   fee,

                   if(OrderDate>=StarDate,-StartDate,StartDate)

              )

               

              After doing couple of checks it seems to be working fine.

              Might not be the cleanest way to proceed but I don't see a problem at this point.

               

              Thanks anyway for your support and let me know what you think about the solution I found, whether it's a good way to approach this or not.

               

              Kind regards

              philippe