topic Re: Left Join: Avoid duplicates in QlikView

Left Join: Avoid duplicates

qlikviewaf — Sat, 16 Nov 2024 18:26:46 GMT

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?

Re: Left Join: Avoid duplicates

Vegar — Thu, 25 Jun 2020 07:37:18 GMT

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;

Re: Left Join: Avoid duplicates

MindaugasBacius — Thu, 25 Jun 2020 08:57:55 GMT

Might help you:

Left Join (sales)

Load Item

,Info

,Max(Sequence) as Sequence

Resident LeftJoinTable

Group By Item, Info;

Re: Left Join: Avoid duplicates

Brett_Bleess — Thu, 09 Jul 2020 12:41:09 GMT

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