2 Replies Latest reply: Dec 19, 2014 9:55 AM by Jérôme CLIGNY RSS

    Fields comparison of multiple files

      Hello everybody.
      I don't know if this question is already answered but my searches stayed unsuccessful, so I need help, please.
      I have to compare a date field value of multiple Excel files.
      It work like this :
      I load date field from Excel files with wildcard and this field (date) can change on every Excel file.
      FROM [U:\Export Pilotage\Journalier\Export_*.xls]
      Every Excel file in named xxx_dd-mm-yyy.xls, and I make a field date_export with each filename :
      Date(Date#(Right(FileBaseName(), 10), 'DD-MM-YYYY')) As Date_export
      So for 1 date_export line I have 1 field date as Besoin_CMD_Corrige
      How can I compare the Besoin_CMD_Corrige of the the last Excel file with the last -1 as a new fiels ?
      I need : if(Besoin_CMD_Corrige of max(date_export) < Besoin_CMD_Corrige of max(date_export) -1,'earlier deadline',null()) but I don't know how ti implement the "of".
      Community, thanks in advance.
      Jérôme
        • Re: Fields comparison of multiple files
          Friedrich Hofmann

          Hi Jerome,

           

          I am not quite clear what your scenario is, but going by what you say, you just need to draw that date ("Besoin_CMD_Corrige") from the record with the largest date?

          That's rather easy:

          - Sort your table (only possible in a RESIDENT LOAD, not in the primary LOAD) by that date in asc. order

          => The largest date will be the very last record.

          - Then use the PEEK() function to get that date into a variable - without a number as parameter (only the fieldname as parameter), it automatically uses the record that was loaded the very last - that would then be the one with the largest date.

          => You can use that variable for your comparison.

          HTH.

          Best regards,

           

          DataNibbler

            • Re: Fields comparison of multiple files

              Hi DataNibbler.

               

              I try the Peek() solution and it work but not 100% for me.

               

              When I have a single valur for "Besoin_CMD_Corrige" field, the peek() function return a wrong date but when It is not single that's work.

               

              Also I only success with the Peek() function when I use it in a chart as expression, not in the script.

               

              Best regards Jérôme