topic How to concatenate and compute variables in QlikView

How to concatenate and compute variables

Thu, 08 May 2014 02:12:06 GMT

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 — Thu, 08 May 2014 03:37:17 GMT

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

SergeyMak — Thu, 08 May 2014 04:50:50 GMT

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.