Load script: One alias, multiple tables

[calebd]

I've got a base transactional table with a numeric DocType field (20=GRPO, 59=Goods Receipt, 60=Goods Issue). Depending on what value is in that field, I'd like to associate the corresponding DocEntry field with a different table (OPDN, OIGN, OIGE respectively) without generating a synthetic key or a loop. What's the best way to handle that within a load script? I've got a common ItemCode field across all of the tables, as well as a lot# field. The alias is primarily so that I can track the item and lot# accurately.

TRX1.DocType	TRX1.DocEntry
20	212
20	214
59	A100

OPDN.DocEntry	OPDN.ItemCode
212	Acme_Anvil1
213	Engine
214	Acme_Anvil5

OIGN.DocEntry	OIGN.ItemCode
A99	Acme_Anvil5
A100	Engine

Thanks for the assist!

[dplr-rn]

Dont know the full scale of the issue but try concatenating OPDN and OIGN table.
maybe create another column to identify it.
e.g.
SomeName:
load DocEntry
, ItemCode
, 'OPDN' as type
from ....OPDN

concatenate(SomeName)
load DocEntry
, ItemCode
, 'OIGN' as type
from ....OIGN

[Marcos_rv · Accepted solution]

Hi, try this:

TRX1:

load DocType ,

DocEntry,

DocType&'-'&DocEntry  as KEY

FROM TRX1 ...

ITEM:

LOAD

'20'&'-'DocEntry  AS KEY

ItemCode

FROM  OPDN ....

CONCATENATE(ITEM)

LOAD 

'59'&'-'DocEntry  AS KEY

ItemCode

FROM OIGN ....

CONCATENATE (ITEM)

....

You would have a single table that joins the transactional according to the code.