1 Reply Latest reply: Oct 27, 2015 4:27 PM by Sunny Talwar RSS

    comparing two fields from two different tables

      Hi .. still a newbie ...

       

      Loading in two tables from two different spreadsheets.

       

      I want to compare a column from table week3 with a column in table week4 and return a column 'compare'

       

      week3:
      LOAD hostname,
      status as wk3status,
      os as wk3os,
      osversion as wk3osversion,
      serialno as wk3serialno,
      discoveredserialno as wk3discserialno,
      parenthostname as wk3hostname
      FROM
      [C:\Users\v374914\Desktop\OVMHistory.xlsx]
      (
      ooxml, embedded labels, table is [10-18-2015]);



      week4:
      LOAD hostname,
      status as wk4status,
      os as wk4os,
      osversion as wk4osversion,
      serialno as wk4serialno,
      discoveredserialno as wk4discserialno,
      parenthostname as wk4hostname
      FROM
      [C:\Users\v374914\Desktop\OVMHistory.xlsx]
      (
      ooxml, embedded labels, table is [10-25-2015]);

      if(wk3serialno <> wk4serialno, 'good','bad');

       

       

      Obviously, that if statement isn't working.  How can I add an additional column to my overall data where if wk3serial <> wk4serialno, then 'nomatch'.

        • Re: comparing two fields from two different tables
          Sunny Talwar

          May be something like this:

           

          week3:
          LOAD hostname,
          status as wk3status,
          os as wk3os,
          osversion as wk3osversion,
          serialno as wk3serialno,
          discoveredserialno as wk3discserialno,
          parenthostname as wk3hostname
          FROM
          [C:\Users\v374914\Desktop\OVMHistory.xlsx]
          (ooxml, embedded labels, table is [10-18-2015]);

          week4:
          LOAD hostname,
          status as wk4status,
          os as wk4os,
          osversion as wk4osversion,
          serialno as wk4serialno,
          discoveredserialno as wk4discserialno,
          parenthostname as wk4hostname
          FROM
          [C:\Users\v374914\Desktop\OVMHistory.xlsx]
          (ooxml, embedded labels, table is [10-25-2015]);


          Temp:

          LOAD hostname,

                    wk3serialno

          Resident week3;


          Join(Temp)

          LOAD hostname,

                    wk4serialno

          Resident week3;


          Table:

          LOAD hostname,

                  if(wk3serialno <> wk4serialno, 'good','bad') as Flag

          Resident Temp;


          DROP Table Temp;