2 Replies Latest reply: May 8, 2014 12:50 AM by Sergey Makushinsky RSS

    How to concatenate and compute variables

      Hi,

       

      I want to concatenate 2 tables with the same column names and compute new columns from existing columns. If I do this:

      Sector0:

      LOAD *,

           Time([Date Confirmed],'hh:mm:ss') as Time,

           Date([Date Confirmed],'M/D/YYYY') as [Actual Date],

           If(time#([Time],'hh:mm:ss')<time#('10:00:00','hh:mm:ss'),1,0) as [Before 10am],

           If(time#([Time],'hh:mm:ss')>time#('22:00:00','hh:mm:ss'),1,0) as [After 10pm]

      FROM

      <file>

      (txt, codepage is 1252, embedded labels, delimiter is '|', msq);


      Concatenate(Sector0)

      LOAD *,

           Time([Date Confirmed],'hh:mm:ss') as Time,

           Date([Date Confirmed],'M/D/YYYY') as [Actual Date],

           If(time#([Time],'hh:mm:ss')<time#('10:00:00','hh:mm:ss'),1,0) as [Before 10am],

           If(time#([Time],'hh:mm:ss')>time#('22:00:00','hh:mm:ss'),1,0) as [After 10pm]

      FROM

      <file2>

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

       

      there will be the error messages "Field not found - <Time>" and "Table not found Concatenate(Sector0)...".

      But if the code is like the one below, the script can successfully load.

       

      Sector0:

      LOAD *,

           Time([Date Confirmed],'hh:mm:ss') as Time,

           Date([Date Confirmed],'M/D/YYYY') as [Actual Date]

      FROM

      <file>

      (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

       

      Concatenate(Sector0)

      LOAD *,

           Time([Date Confirmed],'hh:mm:ss') as Time,

           Date([Date Confirmed],'M/D/YYYY') as [Actual Date],

           If(time#([Time],'hh:mm:ss')<time#('10:00:00','hh:mm:ss'),1,0) as [Before 10am],

           If(time#([Time],'hh:mm:ss')>time#('22:00:00','hh:mm:ss'),1,0) as [After 10pm]

      FROM

      <file2>

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

       

      Thanks.

       

      Rachel

        • Re: How to concatenate and compute variables
          jagan mohan rao appala

          Hi,

          Try like this

           

          Sector0:

          LOAD

          *,

          If(time#([Time],'hh:mm:ss')<time#('10:00:00','hh:mm:ss'),1,0) as [Before 10am],

               If(time#([Time],'hh:mm:ss')>time#('22:00:00','hh:mm:ss'),1,0) as [After 10pm];

          LOAD *,

               Time([Date Confirmed],'hh:mm:ss') as Time,

               Date([Date Confirmed],'M/D/YYYY') as [Actual Date]    

          FROM

          <file>

          (txt, codepage is 1252, embedded labels, delimiter is '|', msq);


          Regards,

          Jagan.

          • Re: How to concatenate and compute variables
            Sergey Makushinsky

            Hi Rachel,

             

            The first error you have because there is no field Time in your file.

            You can use

            Sector0:

            LOAD *,

                 Time([Date Confirmed],'hh:mm:ss') as Time,

                 Date([Date Confirmed],'M/D/YYYY') as [Actual Date],

                 If(Time([Date Confirmed],'hh:mm:ss')<time#('10:00:00','hh:mm:ss'),1,0) as [Before 10am],

                 If(Time([Date Confirmed],'hh:mm:ss')>time#('22:00:00','hh:mm:ss'),1,0) as [After 10pm]

            FROM

            <file>

            (txt, codepage is 1252, embedded labels, delimiter is '|', msq);


            or use the code of