How to concatenate and compute variables

[#1]

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

[jagan]

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.

[SergeyMak]

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 jagan.