topic Re: Table X Left Join with itself in QlikView

Table X Left Join with itself

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

Hello, please help with such situation:

I have table X with columns:

ID Name Friend_ID

1 John

2 Alex

3 MIchel 1

4 Daniel 2

Where Friend_ID is the same values as ID (it showns the same row)

I need to make an SQL quary in order to get next info:

Name Friend_Name

John

Alex

Michel John

Daniel Alex

How can I do it?

Thank you in advance.

Re: Table X Left Join with itself

sunny_talwar — Thu, 31 Mar 2016 12:50:59 GMT

Not sure about the SQL, but in QlikView or Qlik Sense, you can try this:

Table:

LOAD * Inline [

ID, Name, Friend_ID

1, John,

2, Alex,

3, MIchel, 1

4, Daniel, 2

];

Left Join (Table)

LOAD ID as Friend_ID,

Name as Friend_Name

Resident Table;

Re: Table X Left Join with itself

marcus_sommer — Thu, 31 Mar 2016 12:51:19 GMT

Try something like this:

table:

load ID, Name, Friend_ID From x;

left join

load Friend_ID as ID, Name as Friend_Name resident table;

- Marcus

Re: Table X Left Join with itself

sunny_talwar — Thu, 31 Mar 2016 12:53:37 GMT

Method 2 can be using ApplyMap function:

Table:

LOAD * Inline [

ID, Name, Friend_ID

1, John,

2, Alex,

3, MIchel, 1

4, Daniel, 2

];

Mapping:

Mapping

LOAD ID,

Name

Resident Table;

FinalTable:

LOAD *,

ApplyMap('Mapping', Friend_ID, Null()) as Friend_Name

Resident Table;

DROP Table Table;

Re: Table X Left Join with itself

sculptorlv — Thu, 31 Mar 2016 13:11:56 GMT

Can't solve the syntax .. never did LOAD JOIN before .. please help!

My code is:

Table_1:

LOAD

Contract_Number, //It is the name

FA_Class_Name,

RentPrice,

Device_Quantity,

Contract_TS_ID, //It is the ID

Alternative_Contract_TS_ID, // It is the friend_ID

IF(FA_Class_Type = '4.2.05', 'Vending Coffee',

IF(FA_Class_Type = '4.2.06', 'Vending Snack',

IF(FA_Class_Type = '4.2.07', 'Vending Mix',

'error'))) AS FA_Class_Type

WHERE Device_Quantity >0;

Left Join (Table_1)

LOAD

Contract_TS_ID AS Alternative_Contract_TS_ID,

Contract_Number AS Alternative_Contract_Number

Resident Table_1;

SQL SELECT

TABLE_DocumentRows."FA Location SubCode" AS Contract_Number,

SUM(TABLE_DocumentRows."FA Quantity") AS Device_Quantity,

TABLE_DocumentRows."FA Class Code" AS FA_Class_Name,

TABLE_FACLASS."Product Group Code" AS FA_Class_Type,

TABLE_Price."Month Sum" AS RentPrice,

TABLE_Contracts_TS.ID AS Contract_TS_ID,

TABLE_Contracts_TS.AlternativeContractID AS Alternative_Contract_TS_ID

FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_DocumentRows

LEFT JOIN "EUR-VENDEN".dbo."EUR VENDEN$FA Class" AS TABLE_FACLASS

ON TABLE_DocumentRows."FA Class Code" = TABLE_FACLASS.Code

LEFT JOIN "EUR-venden".dbo."EUR VENDEN$Agreement Line" AS TABLE_Price

ON TABLE_Price."Agreement ID" = TABLE_DocumentRows."FA Location SubCode" AND TABLE_DocumentRows."FA Class Code" = TABLE_Price."Device type"

LEFT JOIN "TS_Live".dbo."tbl_Contract" AS TABLE_Contracts_TS

ON TABLE_Contracts_TS.ContractNumber = TABLE_DocumentRows."FA Location SubCode" COLLATE DATABASE_DEFAULT

WHERE

(

TABLE_FACLASS."Product Group Code" = '4.2.05' //Vending Coffee devices

TABLE_FACLASS."Product Group Code" = '4.2.06' //Vending Snack devices

TABLE_FACLASS."Product Group Code" = '4.2.07' //Vendng Combi devices

)

AND

TABLE_DocumentRows."FA Location Type" = '1' //Only client rows

AND

TABLE_Price."End Date"='01.01.1753 0:00:00' //Only active prices

AND

TABLE_Price."Line type"=1 //IM 01.09.15;

GROUP BY

TABLE_DocumentRows."FA Location SubCode",

TABLE_DocumentRows."FA Class Code",

TABLE_FACLASS."Product Group Code",

TABLE_Price."Month Sum",

AlternativeContractID,

TABLE_Contracts_TS.ID

;

Re: Table X Left Join with itself

sunny_talwar — Thu, 31 Mar 2016 13:13:46 GMT

I don't see a from or resident statement. Where is Table_1 getting loaded from?

Re: Table X Left Join with itself

sunny_talwar — Thu, 31 Mar 2016 13:19:35 GMT

Can you try this:

Table_1:

LOAD

Contract_Number, //It is the name

FA_Class_Name,

RentPrice,

Device_Quantity,

Contract_TS_ID, //It is the ID

Alternative_Contract_TS_ID, // It is the friend_ID

IF(FA_Class_Type = '4.2.05', 'Vending Coffee',

IF(FA_Class_Type = '4.2.06', 'Vending Snack',

IF(FA_Class_Type = '4.2.07', 'Vending Mix',

'error'))) AS FA_Class_Type

WHERE Device_Quantity >0;

SQL SELECT

TABLE_DocumentRows."FA Location SubCode" AS Contract_Number,

SUM(TABLE_DocumentRows."FA Quantity") AS Device_Quantity,

TABLE_DocumentRows."FA Class Code" AS FA_Class_Name,

TABLE_FACLASS."Product Group Code" AS FA_Class_Type,

TABLE_Price."Month Sum" AS RentPrice,

TABLE_Contracts_TS.ID AS Contract_TS_ID,

TABLE_Contracts_TS.AlternativeContractID AS Alternative_Contract_TS_ID

FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_DocumentRows

LEFT JOIN "EUR-VENDEN".dbo."EUR VENDEN$FA Class" AS TABLE_FACLASS

ON TABLE_DocumentRows."FA Class Code" = TABLE_FACLASS.Code

LEFT JOIN "EUR-venden".dbo."EUR VENDEN$Agreement Line" AS TABLE_Price

ON TABLE_Price."Agreement ID" = TABLE_DocumentRows."FA Location SubCode" AND TABLE_DocumentRows."FA Class Code" = TABLE_Price."Device type"

LEFT JOIN "TS_Live".dbo."tbl_Contract" AS TABLE_Contracts_TS

ON TABLE_Contracts_TS.ContractNumber = TABLE_DocumentRows."FA Location SubCode" COLLATE DATABASE_DEFAULT

WHERE

(

TABLE_FACLASS."Product Group Code" = '4.2.05' //Vending Coffee devices

TABLE_FACLASS."Product Group Code" = '4.2.06' //Vending Snack devices

TABLE_FACLASS."Product Group Code" = '4.2.07' //Vendng Combi devices

)

AND

TABLE_DocumentRows."FA Location Type" = '1' //Only client rows

AND

TABLE_Price."End Date"='01.01.1753 0:00:00' //Only active prices

AND

TABLE_Price."Line type"=1 //IM 01.09.15;

GROUP BY

TABLE_DocumentRows."FA Location SubCode",

TABLE_DocumentRows."FA Class Code",

TABLE_FACLASS."Product Group Code",

TABLE_Price."Month Sum",

AlternativeContractID,

TABLE_Contracts_TS.ID;

Left Join (Table_1)

LOAD

Contract_TS_ID AS Alternative_Contract_TS_ID,

Contract_Number AS Alternative_Contract_Number

Resident Table_1;

Re: Table X Left Join with itself

sculptorlv — Thu, 31 Mar 2016 13:20:33 GMT

Ok,

i found my mistake... I send second load to the end .. but still, I didn't get necessary result:

Table_1:

LOAD

Contract_Number, //It is the name

FA_Class_Name,

RentPrice,

Device_Quantity,

Contract_TS_ID, //It is the ID

Alternative_Contract_TS_ID, // It is the friend_ID

IF(FA_Class_Type = '4.2.05', 'Vending Coffee',

IF(FA_Class_Type = '4.2.06', 'Vending Snack',

IF(FA_Class_Type = '4.2.07', 'Vending Mix',

'error'))) AS FA_Class_Type

WHERE Device_Quantity >0;

SQL SELECT

TABLE_DocumentRows."FA Location SubCode" AS Contract_Number,

SUM(TABLE_DocumentRows."FA Quantity") AS Device_Quantity,

TABLE_DocumentRows."FA Class Code" AS FA_Class_Name,

TABLE_FACLASS."Product Group Code" AS FA_Class_Type,

....

Left Join (Table_1)

LOAD

Contract_TS_ID AS Alternative_Contract_TS_ID,

Contract_Number AS Alternative_Contract_Number

Resident Table_1;

Re: Table X Left Join with itself

sculptorlv — Thu, 31 Mar 2016 13:21:33 GMT

Yes, I did it, but result is not right:

Re: Table X Left Join with itself

sunny_talwar — Thu, 31 Mar 2016 13:25:05 GMT

Are you sure you have Contract_TS_ID = Alternative_Contract_TS_ID. Need to match exactly

Re: Table X Left Join with itself

sculptorlv — Thu, 31 Mar 2016 13:42:33 GMT

Just cheked... data is ok.

This formula works only, if Friend_ID and ID is equal.

Re: Table X Left Join with itself

sunny_talwar — Thu, 31 Mar 2016 13:45:09 GMT

Right exactly, this will only work when Friend_ID = ID. Are you doing some other match?

Re: Table X Left Join with itself

sculptorlv — Thu, 31 Mar 2016 13:48:19 GMT

No... I mean:

ID Name Friend_ID

3 Alex 3

Then it will work.. otherwise, IF

ID Name Friend_ID

1 Nick 2

2 John

The result will be:

1 Nick 2 -

2 John - -

Re: Table X Left Join with itself

sculptorlv — Thu, 31 Mar 2016 13:56:59 GMT

How does system know, on which field must JOIN be made?

Left Join (Table_1)

LOAD

//Alternative_Contract_TS_ID AS Contract_TS_ID,

Contract_TS_ID AS Alternative_Contract_TS_ID,

Contract_Number AS Alternative_Contract_Number

Resident Table_1;

Re: Table X Left Join with itself

marcus_sommer — Thu, 31 Mar 2016 13:59:44 GMT

The join happens to the same field-names. Beside them have you considered not to join the data and simply just to use two associated tables?

- Marcus

Re: Table X Left Join with itself

sculptorlv — Thu, 31 Mar 2016 14:05:37 GMT

yep, I was thinking about it .. but then all other calculation will be very difficult

Re: Table X Left Join with itself

sculptorlv — Fri, 01 Apr 2016 07:16:53 GMT

I used this construction .. and seems that it works, BUT:

As soon, as I use WHERE statment - connection became lost.

My construction is:

LOAD

Contract_TS_ID,

Contract_Number,

Alternative_Contract_TS_ID;

SQL SELECT

TABLE_Contracts_TS.ID AS Contract_TS_ID,

...

FROM "TS_Live".dbo."tbl_Contract" AS TABLE_Contracts_TS

LEFT JOIN ......TABLE_ANOTHER;

// WHRE TABLE_ANOTHER.Column1 > 0; This corrupt all calculations

Mapping:

Mapping

LOAD

Contract_TS_ID,

Contract_Number

Resident Table_1;

FinalTable:

LOAD *,

ApplyMap('Mapping', Alternative_Contract_TS_ID, Null()) as Alternative_Contract_Number

Resident Table_1;

DROP Table Table_1;

Re: Table X Left Join with itself

Anonymous — Wed, 13 Apr 2016 13:43:40 GMT

I Use this exp

Contact:

Load ContactID,

CountryID,

DepartmentID ,

UniversityID,

FacultyID,

ContactIDCompany,

ContactTypeID,

ContactName,

FirstName,

RTrim(ContactName) &' '& RTrim(MiddleName) &' '& RTrim(FirstName) As Contact_FullName,

MiddleName,

OldName,

UserName,

UserPassword,

UserGroupID,

If(Active=-1,'Y','N') As [Contact_Active],

RoleID,

OrgDeptID As [Contact OrgDeptID],

PrefixID,

JobTitle,

WebPage,

OldNum,

Remark As [Contact Remark],

ExchangeMailBoxName,

DisplayResponsible,

ContactIDResponsible

From Contact

Left Join

Load ContactID ,

ContactIDCompany

From Contact

Left Join

Load ContactID As ContactIDCompany ,

ContactName &' '& RTrim(FirstName) &' '& RTrim(MiddleName) As [Contact_Company]

From Contact