6 Replies Latest reply: May 15, 2014 9:52 AM by Bruce _ RSS

    External Looks ups

    Bruce _

      Hi everyone

       

      Is there a way to do external lookups that are not equal to, but '>=" or '<=' ?

       

      I am looking up currency exchnage rates, but some days are not in the table, so I want Monarch to look up the prior date that has a number.

       

      Any suggestions?

       

      Thanks

        smile.gif[/img]

        • External Looks ups
          Grant Perkins

          Hi Bruce,

           

          Whilst internal lookups probably allow the flexibility you need they are obviously impractical for your purpose. External lookups are much more specific.

           

          Unless of course the format allows some clever concept to be developed.

           

          The nature of exchange rates, in my past experience, is either that they tend to be set and left until such time as a change is appropriate (annual adjustment, quarterly adjustment, monthly adjustment, etc.) or there is a daily list normally for all rates or, to be a little more cautious about that, a fully populated table although some rates may be carried over on some days.

           

          The latter example suggests that a direct lookup based on date should always be possible - anything else may indicate an error situation.

           

          The former example is a little trickier but suggests a primary lookup on a currency code with a secondary check against a date somewhere if the transaction date is not 'today'. (For example.)

           

          So the question might be - if you don't have a complete lookup table, why not?

           

          If that is likely to be a regular event and IF the rule for a missing value would be to use the value from th previous day, then one solution might be to build your own version of the external table (using Monarch of course!) before running your main process. (Would work best if it was run every day or at least very work day ...) So once you have generated a first fully populated table you would simply update that from the 'official' external source and populate any missing values with whatever the previous table had for the day before.

           

          That would, hopefully, give you a fully populated table for all dates and leave you with a simple date and currency based lookup for you main process.

           

          'Thinking' aloud. It's late over here .. there may well be better answers but that's the one that came to me as a catch-all.

           

          Could be a great application for Data Pump!

           

           

          Grant

           

           

          Originally posted by Bruce:

          Hi everyone

           

          Is there a way to do external lookups that are not equal to, but '>=" or '<=' ?

           

          I am looking up currency exchnage rates, but some days are not in the table, so I want Monarch to look up the prior date that has a number.

           

          Any suggestions?

           

          Thanks

             smile.gif[/img]  /b[/quote]

          • External Looks ups
            Bruce _

            Grant

             

            Thanks for the insight.

             

            I was using an external Excel table to look up currency exchange data, but some days are missing due to holidays and weekends. We log an order date as the date the customer sends us the order even if we are not here to receive it.

             

            I was manually going into excel & plugging the missing dates by copying data down & was hoping for an easier method.

             

            Regards,

            • External Looks ups
              Grant Perkins

              Bruce,

               

              I thought perhaps something along those lines.

               

              How about an Excel macro or something that automatically updates every 'empty' day if no 'manual' adjustments have been provided?

               

              Or do much the same by importing the Excel sheet to Monarch, add the missing dates and 'fill' the empty cells then export back to Excel (ready for the following day ...).

               

              Probably doesn't make much sense but may provoke some ideas ...

               

               

              Grant

               

               

              Originally posted by Bruce:

              Grant

               

              Thanks for the insight.

               

              I was using an external Excel table to look up currency exchange data, but some days are missing due to holidays and weekends. We log an order date as the date the customer sends us the order even if we are not here to receive it.

               

              I was manually going into excel & plugging the missing dates by copying data down & was hoping for an easier method.

               

              Regards, /b[/quote]

              • External Looks ups
                michaeljul _

                You could also check the order data and if a saturday or sunday, then add 2 or 1 days respectively to it befor looking up the exchange rate in the extrrnal table - that way you would apply mondays exchange rate to the sat/sun orders......

                • External Looks ups
                  Bruce _

                  Michael

                   

                  That sounds promising, but the accounting people also skips days at random (the exchnage rate did not move much, or they forgot).

                   

                  I'm trying to find an automatic way to pull of a date & if the exchange rate is missing (holiday, weekend, lazyiness), pull off the last rate listed.

                   

                  Unless I import the table as a text file, and use the "fill empty cells with value from previous record"...I just thought about this as I typed, it might be worth a check.

                   

                  Thanks everyone

                  • External Looks ups
                    Bruce _

                    Solved it.

                     

                    Used Excel & vlookup form a sheet with everydate & looked up to another sheet which has the Monarch export with missing numbers.

                     

                    Thanks again everyone for suggestions