Table X Left Join with itself

[sculptorlv]

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.

[sunny_talwar]

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;

[marcus_sommer]

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

[sunny_talwar]

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;

[sculptorlv]

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

  OR

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

  OR

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

;

[sunny_talwar]

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

[sunny_talwar]

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

  OR

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

  OR

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;

[sculptorlv]

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;

[sculptorlv]

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

[sunny_talwar]

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