4 Replies Latest reply: Oct 7, 2015 10:15 AM by RSS

    Setting field to define source

      i have three spreadsheets.

       

      I want to load the spreadsheets and set an additional field so I know the source.

       

      Below is my logic for loading the files

       

      Crossreference:
      LOAD ASSETNAME as 'hostname',
      FROM
      [C:\Users\myfile.XLSX]
      (
      ooxml, embedded labels, table is Sheet1);

       

      noconcatenate;

       

       

      snapshot:

      LOAD ASSETNAME as 'hostname',
      FROM
        [C:\Users\myfile.XLSX]]
      (
      ooxml, embedded labels, table is Sheet2);

       

      noconcatenate;

       

      assetinv:

      LOAD ASSETNAME as 'hostname',
      FROM
      [C:\Users\myfile.XLSX]
      (
      ooxml, embedded labels, table is Sheet3);

       

       

      If I join all three tables together, I get a master list of hostnames, but I lose the source of which file it resides in.

       

      How can I create 'something' so I can create a table object and be able to see which file had the data.

       

       

        • Re: Setting field to define source
          Sunny Talwar

          May be try this:

           

          Crossreference:
          LOAD ASSETNAME as 'hostname',

                    'Crossreference' as Source
          FROM
          [C:\Users\myfile.XLSX]
          (
          ooxml, embedded labels, table is Sheet1);

           

          Concatenate(Crossreference)

          LOAD ASSETNAME as 'hostname',

                   'Snapshot' as Source
          FROM
            [C:\Users\myfile.XLSX]]
          (
          ooxml, embedded labels, table is Sheet2);

           

          Concatenate(Crossreference)

          LOAD ASSETNAME as 'hostname',

                    'Assetinv' as Source
          FROM
          [C:\Users\myfile.XLSX]
          (
          ooxml, embedded labels, table is
          Sheet3);

          • Re: Setting field to define source
            Massimo Grossi

            add a field for source

             

            Crossreference:
            LOAD

                 ASSETNAME as 'hostname',

                 1 as 1stSource

            FROM
            [C:\Users\myfile.XLSX]
            (
            ooxml, embedded labels, table is Sheet1);

             

               1 as 2ndSource

               1 as 3rdSource

            • Re: Setting field to define source
              Nagaian Krishnamoorthy

              Try the following:

              Crossreference:
              LOAD ASSETNAME as 'hostname',
              FROM [C:\Users\myfile.XLSX]
              (
              ooxml, embedded labels, table is Sheet1);

               

              LOAD ASSETNAME as 'hostname',
              FROM   [C:\Users\myfile.XLSX]]
              (
              ooxml, embedded labels, table is Sheet2);

               

              LOAD ASSETNAME as 'hostname',
              FROM [C:\Users\myfile.XLSX]
              (
              ooxml, embedded labels, table is Sheet3);

              • Re: Setting field to define source

                Thanks for the suggestions on the FirstSource, SecondSource, ThirdSource

                 

                My challenge now .. if I display the joined data in a Table Box, how can I search/filter where the hostname is only in FirstSource or the hostname is in the ThirdSource.

                 

                I think the blanks or nulls as a result of a join are stopping me from being able to filter on it.