Joining table data

[harjedalskok]

Hi,

i wonder how i can join table data from different tables within the loadscript

I would like to have a table that Shows for example this two values combined in same table.

OrderNo as Ordernr, (Ordertable)

strFaultCodeDesc as Felkodstyp, (Faultcodestable)

Below are the actual loadscript for now.

Would be very glad if someone could enlight me.

Thx in Advance.

Order:

LOAD

OrderId,

OrderNo as Ordernr,

OrderTypeId,

if(IsNull(MountedDate), 'Nej', 'Ja') as Monteringsprickat

;

SQL

EXEC QV_ORDER_BY_SALESDATE $(vFromDate);

Suborder:

Load

fkOrderheadingID as OrderId,

pkSubOrderHeadingID as SubOrderId,

fkSubFaultCode as SubFaultId;

SQL SELECT fkOrderheadingID, pkSubOrderHeadingID,fkSubFaultCode

FROM wo.dbo.tblSubOrderHeading;

SubFaultcodes:

Load

fkFaultCode as FaultCodeId,

pkSubFaultCode as SubFaultId,

strSubFaultDescription as Felkod;

SQL SELECT fkFaultCode, pkSubFaultCode, strSubFaultDescription

FROM wo.dbo.tblSubFaultCode;

Faultcodes:

Load

pkFaultCode as FaultCodeId,

strFaultCodeDesc as Felkodstyp,

if(strFaultCodeDesc = 'Säljare', strFaultCodeDesc, null()) as Säljfelkod ;

SQL SELECT pkFaultCode, strFaultCodeDesc

FROM wo.dbo.tblFaultCodes;

[Kushal_Chawda]

what is the key to join the table?

[vkhassanov]

Hi,

Actually all tables will become linked via the fields having the same name and you will get properly populated table object with Ordernr and Felkodstyp in the document.

However, if you need to join the tables 'hard way' during the load then here you are:

NewTable:

NoConcatenate

LOAD

     OrderId,

     Ordernr

Resident Order;

JOIN

LOAD Distinct

     OrderId,

     SubFaultId

Resident  Suborder;

JOIN

LOAD Distinct

     SubFaultId,

     FaultCodeId

Resident SubFaultcodes;

JOIN

LOAD Distinct

     FaultCodeId,

     Felkodstyp

Resident Faultcodes;

DROP Fields OrderId, SubFaultId, FaultCodeId from NewTable;

[harjedalskok]

Hi and thanks,

Yes they are properly configured with rest of the script, but my issue is that i need to make an "if" statement within the same table with fields from the two tables. Should i put in the "Felkodstyp" in the newtable?

I will try above. Below you can see the complete datatree

Thx alot for now.