2 Replies Latest reply: May 9, 2017 4:59 AM by Vineeth Pujari RSS

    check missing values

    Agrim Sharma

      Hi,

       

      I have an excel file with two sheets, i want to get those values which are not common in both the field,

      where they are case sensitive,

        eg

       

      Assetname , Item

              YOu    , you

                My    , my

       

      my script is-

      LOAD

          Assetname,

          Statename,

          Lastchanged

      FROM [lib://t1/web40reppartofdomain1.xls]

      (biff, embedded labels, table is report$);

       

       

      LOAD

          Catégorie,

          Item,

          "Date d'installation"

      FROM [lib://t1/web40reppartofdomain1.xls]

      (biff, embedded labels, table is Feuil1$);

       

       

      i want to match Item and Assetname and get those values which are not common in both of them.

      and want an output as "Yes-matched all" & "No-All not matched". in these sheets there is nothing common(Key) but there is date fields in both of them

        • Re: check missing values
          Andrey Khoronenko

          Hi Agrim,

           

          As variant, you can use Set Analisys expression to work similarly to the island of data. In attached file I gave an example of selecting only the same records in fields [Assetname] and [Item] (Table 3) and the same records selection with the state of field Statename as 'Non-active' (Table 4).

          Similar to this approach, you can make the other selections you need.

           

          Regards,

          Andrey

          • Re: check missing values
            Vineeth Pujari

            this

             

            map_report:

            Mapping LOAD upper(Assetname) as Assetname,

              'Exists in Report'

            FROM

            [C:\Users\vp51284\Downloads\web40reppartofdomain1.xls]

            (biff, embedded labels, table is report$);

             

             

             

             

            map_Feuil1:

            Mapping LOAD

                upper(Item) as ITEM,

                'Exists in Feuil1'

            FROM

            [C:\Users\vp51284\Downloads\web40reppartofdomain1.xls]

            (biff, embedded labels, table is Feuil1$);

             

             

             

             

            Tab_Feuil1:

            LOAD Catégorie,

                Item,

                [Date d'installation],

                ApplyMap('map_report',upper(Item),'Not Exist in Report') as ReportExistsFlag

            FROM

            [C:\Users\vp51284\Downloads\web40reppartofdomain1.xls]

            (biff, embedded labels, table is Feuil1$);

             

             

             

             

            LOAD Assetname,

                Statename,

                Lastchanged,

                      ApplyMap('map_Feuil1',upper(Assetname),'Not Exist in Feuil1') as Feuil1ExistsFlag

            FROM

            [C:\Users\vp51284\Downloads\web40reppartofdomain1.xls]

            (biff, embedded labels, table is report$);