9 Replies Latest reply: Apr 15, 2014 8:16 AM by Roland Kunle RSS

    Comparing data

      Hi,

      I need to compare two tables from different sources (textfile, excel file) to see if they match.
      If they don't match I'd like to show the difference in another listbox or with highlighting.... or someting else.

      I have a textfile(file1) with data, and an excelfile(file2) with data.
      Most fields are named the same, at least the importent fields that I need to compare.
      I want to make sure that the following fields has the same data in them in both files.
      + ID_Nr
      + ID_Name

      ID_Name is a string.
      ID_Nr is integer (sometimes starting with one or two '0' zeros)

      I need to be able to compare them individually (and display them individually) as well as together.
      I mean compare ID_Nr in both files, display difference and compare ID_Name in both files, display difference.

      What is the best way to do this?

      Thank you

        • AW:Comparing data

          Hello Johan,

          there are more than one way. I would prefer using a "not exists". For performance reasons I would do a qualified load from both external resources (if you like, delete them afterwards). Next step is a resident load to get the missing rows. Please take a look at the following code snippet:

           

          QUALIFY *;

          textdata:
          Load * inline
          [
          ID, Name
          1, EINS
          2, ZWEI
          3, DREI
          7, SIEBEN
          8, ACHT
          ];


          exceldata:
          Load * inline
          [
          ID, Name
          1, Eins
          2, Zwei
          3, Drei
          4, Vier
          ];


          MissingInExcel:
          LOAD
          *
          Resident textdata
          Where not Exists(exceldata.ID, textdata.ID);

          MissingInText:
          LOAD
          *
          Resident exceldata
          Where not Exists(textdata.ID, exceldata.ID);


          Regards, Roland