5 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Link to Largest Date

    alexcn _

      Is there anyway to link two tables where one date (DateA) needs to be compared to another where that date (DateB) is the largest date which is smaller than DateA?

        • Link to Largest Date
          Data Kruncher

          Hi Alex,

           

          If I understand this correctly, DateB is meant to be larger (more recent) than DateA, but there exceptions you need to isolate where DateB is prior to DateA. Correct?

           

          Initially I'd be tempted to export the DateB data out to an Excel file, or an Access database, whichever your data permits.

           

          Then in your DateA model, build an external lookup to the DateB data using a common transaction number or some such item to bring the related DateB field into your DateA data set.

           

          Finally, build either a calculated field and/or a filter, again, based on your needs, which will help you isolate the records where DateB < DateA.

           

          HTH,

          Kruncher

          • Link to Largest Date
            alexcn _

            Moring Kruncher,

             

            Many thanks for your follow up.  Few comments:

             

            1) DateA will usually be the current date or most recent date.

            2) DateB will be the preceeding business day (which is usually DateA -1 unless its a Monday then it will be DateA-2 or even DateB-3 if its a bank holiday)

            3) There is no common reference number, the lines are all numbered individually.

            4) I was thinking of using the date contained in the report name but this further complicates things as THAT is the date the report was created.  For example: Report Date is 14th May as it was run early this morning, DateA is 11th May as its the last business day, DateB is 10th May is its the preceeding business day.

             

            Sorry to be a pain,

             

            Regards,

             

            Alex

            Originally posted by Data Kruncher:

            Hi Alex,

             

            If I understand this correctly, DateB is meant to be larger (more recent) than DateA, but there exceptions you need to isolate where DateB is prior to DateA. Correct?

             

            Initially I'd be tempted to export the DateB data out to an Excel file, or an Access database, whichever your data permits.

             

            Then in your DateA model, build an external lookup to the DateB data using a common transaction number or some such item to bring the related DateB field into your DateA data set.

             

            Finally, build either a calculated field and/or a filter, again, based on your needs, which will help you isolate the records where DateB < DateA.

             

            HTH,

            Kruncher /b[/quote]

            • Link to Largest Date
              Grant Perkins

              Alex,

               

              I'm really lacking in time to check this but some of the samples provided for User Defined Functions in V9 relate to calculating working days using critia that define what the working days are and also any holidays and so on if applicable.

               

              It is possible the 'new for V9' DataAdjust function is in there somewhere but it may not be.

               

              Based on your extended description it occurs to me that some sort of formula that calculates the most likely previous working day could be useful to you. You could then use that for the lookup comparison.

               

              If you then had an IF() formula that checked for other alternatives if the expected link 'failed' you could have a way forward - providing that such loose methods would be acceptable.

               

              HTH.

               

               

              Grant

              • Link to Largest Date
                alexcn _

                Many thanks for the reply - while your answer would work in a single geographic location, the data I am dealing with relates to international business days across the world meaning a holiday could conceivably be any day of the week.

                 

                If I could look up the maximum in the column that would be great, but in the summary view it doesnt work.  Even doing a multipass approach would be wrong as there is no clear definition of linking.

                • Link to Largest Date
                  Grant Perkins

                  Originally posted by alexcn:

                  Many thanks for the reply - while your answer would work in a single geographic location, the data I am dealing with relates to international business days across the world meaning a holiday could conceivably be any day of the week.[/quote]OK, but there is not reason why you should not have as many variations on the work day calculation definition  - one for each geo location. Providing you data has a geo location code of of course! Od some way of deriving it.

                   

                  Originally posted by alexcn:

                   

                  If I could look up the maximum in the column that would be great, but in the summary view it doesnt work.  Even doing a multipass approach would be wrong as there is no clear definition of linking. /b[/quote]Given the above problem with identifying the previous working day, how does max date help unless you know the goegraphic location? (I think I am missing some understanding here.)

                   

                  You could use the summary to find Max Date, then export that and use it in a second pass. If you don't have an obvious link in the data can you create a dummy link (add a calculated field with a faux code that you also create in the summary output) to facilitate the lookup?

                   

                  Grant