TABLE JOIN

[#1]

Hi There,

I am trying to load data from various DB files of which contains the same data structure. In my different DB's I want to join different tables together to get data into one big item master table, instead of sitting with a lot of synthetic keys.

My script at this stage looks like this, it works perfectly for the first region when the 'NEXT REGION' bit is removed. BUT, when it is added, it doesn t join correctly anymore, and also no errors are given. It also creates 4 synthetic keys.

My Code;

// OITM //
FOR EACH REGION IN 'Amanzi','Cape Town','Gauteng','Goschem','Group','Industrial','Kwazulu Natal','North West','Tetralon'
ITEMMASTER:

LOAD
'$(REGION)' as Company,
ItemCode,
ItemName,
ItmsGrpCod
FROM
[$(vQVDPath)$(REGION)\OITM.QVD]
(qvd);

// OITW //
Left Join (ITEMMASTER)
LOAD
'$(REGION)' as Company,
ItemCode,
WhsCode,
OnHand
FROM
[$(vQVDPath)$(REGION)\OITW.QVD]
(qvd);

// OWHS //
Left Join (ITEMMASTER)
LOAD
'$(REGION)' as Company,
WhsCode,
WhsName
FROM
[$(vQVDPath)$(REGION)\OWHS.QVD]
(qvd);

// OITB //
Left Join (ITEMMASTER)
LOAD
'$(REGION)' as Company,
ItmsGrpCod,
ItmsGrpNam
FROM
[$(vQVDPath)$(REGION)\OITB.QVD]
(qvd);

NEXT REGION

Is there any way someone can tell me what I am doing wrong to get this results correctly?

Thanks,

Wynand.

[#2]

Hi Wynand,

I´m not sure if I am going to reply you correctly, but I thing you must use concatenate statement if you relly want to build a main table. I take for sure you have same values on same fields...

That´s all I can tell you.

I hope it helps you, Fiber9906

[Anonymous]

Hi.

I've a QVW file that attacks some QVD with this script:

"Name of the main table":
LOAD * FROM \\xxxx\1.qvd (qvd);
Concatenate ("Name of the main table")
LOAD * FROM \\xxxx\2.qvd (qvd);
Concatenate ("Name of the main table")

Maybe it will work for you.

Regards.

[#4]

Hi,
Your table ITEMMASTER is loaded using several steps, in the first one you have 4 fields, and you add 5 more fields during the other select statements. So the table ITEMMASTER contains 9 fields at the end of the first iteration. When the second one starts you load the 5 first fields in a table ITEMMASTER, but for QV this table contains 9 fields, so it is a different one, called ITEMMASTER-1, where the others fields are added, then you have two tables with different names and same fields, so some synthetic keys.
You have to load your data in a temporary table first, try something like this :

FOR EACH REGION IN 'Amanzi','Cape Town','Gauteng','Goschem','Group','Industrial','Kwazulu Natal','North West','Tetralon'
Tmp_ITEMMASTER:

LOAD
'$(REGION)' as Company,
ItemCode,
ItemName,
ItmsGrpCod
FROM
[$(vQVDPath)$(REGION)\OITM.QVD]
(qvd);

// OITW //
Left Join (ITEMMASTER)
LOAD
'$(REGION)' as Company,
ItemCode,
WhsCode,
OnHand
FROM
[$(vQVDPath)$(REGION)\OITW.QVD]
(qvd);

ITEMMASTER:
Load
Company,
ItemCode,
ItemName,
ItmsGrpCod,
WhsCode,
OnHand,
RowNo() as RowNo
resident Tmp_ITEMMASTER;

Drop table Tmp_ITEMMASTER;

NEXT REGION

I just have added rowno field to avoid a concatenation.

[#5]

Hi,

Thanks for the reply, your suggestion seems to be working just fine, except that now I am struggeling to get the mapping done to bring in Group Name and Warehouse Name. As soon as I try to do the mapping, it created a new table again with -1.

Problem is that I can not map it afterwards as the warehouse code and names differ for all regions, so it has to be done in step 1 where it is running per region.

Regards,

Wynand.