Left Join: Avoid duplicates

[qlikviewaf]

Hi,

i would like to do a left join and avoid duplicates.

I would like to join using the "item" but as you can see on the "left-join" table for the same item there are more than 1 record. What i would like to do is to join using only the most recent value in case more than 1 item are found: so what is in sequence 1-4 will be ignored.

there is any way i can do this?

[Vegar]

You could use FirstSortedValue() to solve this.

Sales:
LOAD Item, Amount
FROM Sales;

LEFT JOIN (Sales)
LOAD 
Item,
FirstSortedValue ( Info, -Sequence) as Info
FROM LeftJoinTable
GROUP BY Item;

[MindaugasBacius]

Might help you:

Left Join (sales)

Load Item

,Info

,Max(Sequence) as Sequence

Resident LeftJoinTable

Group By Item, Info;

[Brett_Bleess]

You have two possible solutions posted, we would greatly appreciate it if you would return to your thread and close things out by using the Accept as Solution button on the post(s) that helped you with the use case.  If you did something different, you can post that and then mark that using the button, and if you still have further questions, please leave an update post.

Regards,
Brett