topic Re: Joining table data in QlikView

Joining table data

harjedalskok — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Joining table data

Kushal_Chawda — Tue, 26 Sep 2017 13:35:31 GMT

what is the key to join the table?

Re: Joining table data

vkhassanov — Tue, 26 Sep 2017 13:58:52 GMT

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;

Re: Joining table data

harjedalskok — Wed, 27 Sep 2017 08:12:17 GMT

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.