Concatenation help needed!

[#1]

Hi guys,

I have a problem in my code where we are trying to concatenate two tables and create a primary key:

DepartmSys:

LOAD

Sys & Departm as %DepartmSysKey,

Sys as DepartmSys,

Departm as Dept,

Flag

FROMSys & Dept as %DepartmSysKey,

[file.xls] (biff, embedded labels, table is Sheet1$);

b:

LOAD

Sys as DepartmSys,

Dept,

0 AS Flag

RESIDENT

TableC;

CONCATENATE

(DepartmSys)

LOAD

* RESIDENT b

where

NOT Exists(%DepartmSysKey);

-------------------------------------------------------------------------------

I was wondering if anyone would have some ideas on this?

Thanks in advance 🙂

bc

[Oleg_Troyansky]

Well, I see 2 problems:

1. You are not calculating your key in the table "b" (or, the order of lines got messed up in your message).

2. You can't use not exists() logic in this case - as a field, the value would exist in one or the other table. You should use a different syntax. Try the following:

DepartmSys:
LOAD
Sys & Departm as %DepartmSysKey,
Sys as DepartmSys,
Departm as Dept,
Flag

 FROM [file.xls] (biff, embedded labels, table is Sheet1$);
b:

 LOAD

Sys as DepartmSys,
Dept,
0 AS Flag
RESIDENT

TableC; 

CONCATENATE
(DepartmSys) 
LOAD
*,
Sys & Dept as %DepartmSysKey
RESIDENT b 
where
NOT Exists(%DepartmSysKey, Sys & Dept);

[#3]

Hi Oleg,

When this syntax is used it says that the DepartmSys table cannot be found for some reason!

Any thoughts?

[Oleg_Troyansky]

can you upload your document or copy your script completely?

[#5]

DepartmentSystem:

LOAD

System

& Department as %DepartmentSystemsKey

,

System

as DepartmentSystem

,

Department

as Dept

,

Flag

FROM

(biff, embedded labels, table is Sheet1$)

;

b:

LOAD

System

as DepartmentSystem

,

Dept

,

0

AS

Flag

RESIDENT

TableCatalogue;

CONCATENATE

(DepartmentSystem)

LOAD

*

,

System

& Dept as

%DepartmentSystemsKey

RESIDENT

B

where

NOT EXISTS(%DepartmentSystemsKey, System & Dept)

;

This is the code (ps, changed field names slightly) I used and the error msg was as follows:

Table not found

CONCATENATE(DepartmentSystem)......etc

thanks for help 🙂

[Oleg_Troyansky]

One of the two:

1. Either you spelled your table name differently (since you renamed your fields slightly, it's hard to say). For example, I noticed, that your temporary table is called "b" in one place and "B" in another - could that be your problem? Table Names are case sensitive...

2. Or you have another table loaded before DepartmentSystem, with the same set of fields, which causes your loaded data to be automatically concatenated with the previously loaded data under another, previously specified, table name.

Another thing you might need to do is enclose the field name into single quotes within EXISTS:

NOT EXISTS('%DepartmentSystemsKey', System & Dept)

cheers,

Oleg

[#7]

Hi Oleg,

The table loads but does not give the answers we would expect to see unfortunately..This is the complete page script, I don't know if this would help or not:

Once again, I appreciate your help 🙂

TableCatalogue:
LOAD
CatalogueId,
LocalityDesc AS Location,
SystemShortName as System,
SystemShortName as irSystem,
UnitDesc,
IF(UnitDesc = 'Debt and Treasury Technology' , 1, 0) as DTT_Flag,
UnitDesc as %UnitJoin,
DepartmentDesc as %DeptJoin,
DepartmentDesc as Dept,
if(index(DepartmentDesc, ' EMEA', -1)>0, left(DepartmentDesc,index(DepartmentDesc, ' EMEA', -1)),
if(index(DepartmentDesc, ' AP', -1)>0, left(DepartmentDesc,index(DepartmentDesc, ' AP', -1)),
if(index(DepartmentDesc, ' AM', -1)>0, left(DepartmentDesc,index(DepartmentDesc, ' AM', -1)),
DepartmentDesc
)
)
) as DeptShort,
DepartmentHead as DeptHead,
TeamDesc as Team,
ApplicationManager,
LastUpdatedDate as GelLastUpdatedDate
WHERE
EXISTS (CatalogueId);
SELECT
CatalogueId,
LocalityDesc,
SystemShortName,
UnitDesc,
DepartmentDesc,
DepartmentHead,
TeamDesc,
ApplicationManager,
LastUpdatedDate
FROM
GEL.dbo.vw_Catalogue

WHERE
LastUpdatedDate > dateadd(year, -1,convert(datetime,cast(datepart(yy,getUTCDate()) as varchar) + cast('-01-01' as varchar)));;

STORE TableCatalogue into Gel.qvd;

TableImpactedCatalogue:
LOAD
CatalogueId as PrimaryCatalogueId,
System as ImpactedBySystem

RESIDENT TableCatalogue;

DepartmentSystem:
LOAD
System & Department as %DepartmentSystemsKey,
System as DepartmentSystem,
Department as Dept,
Flag
FROM (biff, embedded labels, table is Sheet1$);

B:
LOAD
System as DepartmentSystem,
Dept,
0 AS Flag
RESIDENT TableCatalogue;

CONCATENATE (DepartmentSystem)
LOAD
DepartmentSystem & Dept as %DepartmentSystemsKey,
*
RESIDENT B
where NOT EXISTS('%DepartmentSystemsKey', DepartmentSystem & Dept);

[Oleg_Troyansky]

What is the expected result? you never explained what you are trying to do...

Oleg

[#9]

Oleg,

We have two lists of data from different sources (one excel and the other a resident table) We would like one table to overwrite the other and update with additional data.

We have attached a very small example qvw that I have created. (ps - in this example the data is from two inline tables)

When this tries to reload it now says 'table not found' as you will see.

Any info as to why this might be happening would be great.

[Oleg_Troyansky]

See attached. You had 2 problems there:

1. The 2 tables had exactly the same strucutre, so by default they were automatically concatenated. You need to use prefix NOCONCATENATE to avoid it.

2. One of the fields was renamed in one place but not in the other, which caused "Field not found" error.

The logic that you have there will append the first list with the missing records from the second list. Not sure about "overriding" that you'd mentioned...

Oleg