Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
Hi Oleg,
When this syntax is used it says that the DepartmSys table cannot be found for some reason!
Any thoughts?
can you upload your document or copy your script completely?
DepartmentSystem:
LOAD
System
& Department as %DepartmentSystemsKey,
System
as DepartmentSystem,
Department
as Dept,
Flag
FROM
;
b:
LOAD
System
as DepartmentSystem,
Dept
,
0
ASFlag
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 🙂
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
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
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);
What is the expected result? you never explained what you are trying to do...
Oleg
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.
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