Join 2 resident tables

[davyqliks]

Hi,

I have the following 2 tables pulled from resident loads:

Sustainable Grouped and Others

i need to get the value Others into a table with the values from the Sustainable Grouped table.

this is my effort but i am no getting the required result.

Can anyone assist please? i'm not sure im using the correct join function.

NoConcatenate
[All Fibre]:
Load

[Cotton - Organic] & '' & [Polyester Recycled] & '' & [Viscose LENZING™ ECOVERO™] & '' & [Lyocell Tencel®] as [Sustainable Grouped],

Order_NoNum

Resident

[Sustainable Grouped]

;
inner join ([Sustainable Grouped])

Load

Others,
Order_NoNum
Resident
[Merge Fibre]
;

thank you in advance.

Daniel

[joaopaulo_delco]

Hello @davyqliks !

    Try this way:

[All Fibre]:
Load
[Cotton - Organic] & '' & [Polyester Recycled] & '' & [Viscose LENZING™ ECOVERO™] & '' & [Lyocell Tencel®] as [Sustainable Grouped],
Order_NoNum
Resident
[Sustainable Grouped];
inner join ([All Fibre])
Load
Others,
Order_NoNum
Resident
[Merge Fibre];

[davyqliks]

HI,

Thanks so much for taking the time.

this has not worked unfortunately.

the following:

NoConcatenate
[All Fibre]:
Load

[Cotton - Organic] & '' & [Polyester Recycled] & '' & [Viscose LENZING™ ECOVERO™] & '' & [Lyocell Tencel®] as [Sustainable Grouped],

Order_NoNum

Resident

[Sustainable Grouped]
;

inner join ([All Fibre])
Load
Others,
Order_NoNum
Resident
[Merge Fibre];

has resulted in 

here is the table view,

thank you for your time, it is much appreciated.

davyqliks

[PradeepK]

Please go through following links :

Joins in Qlik 

use-mapping-as-alternative-join 

Now about the issue..  

• Syntax is incorrect .. inner join (<Table_Name>)
• Check if you actually have any common Order_NoNum values in both tables. As per provided Screenshot it seems like there are no common values.. i.e. join  is returning NULL
• If you only want to map one field into another table.. It's best to us Mapping Load. It provides option to handle missing case.

I hope this helps.

[davyqliks]

Hi,

Thanks alot for the explaination.

my issue with mapping is the 'Others' is from another table.

I have 3 fibre sections with multiple fibres and i need to separate the specified and call all others 'Others'

like this:

if(Len(Trim("FIBRE 1")) > 0 and not wildmatch(trim("FIBRE 1"),
'*Cotton – Organic*' , ' Cotton – Organic' ,'Cotton – Organic ' ,
'*Polyester - Recycled*' , ' Polyester - Recycled' ,'Polyester - Recycled ' ,
'*Viscose LENZING™ ECOVERO™*' , ' Viscose LENZING™ ECOVERO™' ,'Viscose LENZING™ ECOVERO™ ' , 'Viscose LENZING(tm)ECOVERO(tm)',
'*Lyocell Tencel®*', ' Lyocell Tencel®' , 'Lyocell Tencel® '),
'Others1',"FIBRE 1") as Fibre1,

if(Len(Trim("FIBRE 2")) > 0 and not wildmatch(trim("FIBRE 2"),
'*Cotton – Organic*' , ' Cotton – Organic' ,'Cotton – Organic ' ,
'*Polyester - Recycled*' , ' Polyester - Recycled' ,'Polyester - Recycled ' ,
'*Viscose LENZING™ ECOVERO™*' , ' Viscose LENZING™ ECOVERO™' ,'Viscose LENZING™ ECOVERO™ ' , 'Viscose LENZING(tm)ECOVERO(tm)',
'*Lyocell Tencel®*', ' Lyocell Tencel®' , 'Lyocell Tencel® '),
'Others1',"FIBRE 2") as Fibre2,

if(Len(Trim("FIBRE 3")) > 0 and not wildmatch(trim("FIBRE 3"),
'*Cotton – Organic*' , ' Cotton – Organic' ,'Cotton – Organic ' ,
'*Polyester - Recycled*' , ' Polyester - Recycled' ,'Polyester - Recycled ' ,
'*Viscose LENZING™ ECOVERO™*' , ' Viscose LENZING™ ECOVERO™' ,'Viscose LENZING™ ECOVERO™ ' , 'Viscose LENZING(tm)ECOVERO(tm)',
'*Lyocell Tencel®*', ' Lyocell Tencel®' , 'Lyocell Tencel® '),
'Others1',"FIBRE 3") as Fibre3,

i then pull the Others1 together

like this

if(Len(Trim([Fibres])) > 0 and wildmatch(trim([Fibres]),
'Others1Others1Others1' ,
'Others1Others1' ,
'Others1'
)
, 'Others',
[Fibres]) as OtherMaterials,

so i cannot simply add a mapping ( i dont think).

I will continue to try with the join and check your comment. thanks for your time and help.

Davyqliks

[davyqliks]

Hi Pradeep,

I am reviewing the tables and the two tables i need to merge both have the key field Order_NoNum linking the tables

I just need to join the 2 tables and rename with the values:

Others
Cotton Organic
Polyester.....
Viscose.....
Lycocell

Question - 

Does it look like i have the linked data to be able to achieve this join?

Thank you

D