topic Re: Join issue in QlikView

Join issue

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I have to left join two tables lets say a and b.

a has fields bill, facility and some other fields.

bill facility

1233 12

1233 13

1234 11

1234 12

1235 12

1236 12

1237 12

and in second table I have bill, creditdebits, revenue and discounts.

temp:

load

bill,

sum(credit_debit) as cd,

sum(discounts) as discounts1,

sum(_ev) as profit

Resident b

Group by bill;

left join (a)

load

* resident temp;

problem is the table a has multiple occurrences of bill but I want to join with only one of those

Re: Join issue

sunny_talwar — Mon, 18 Apr 2016 14:44:00 GMT

Which one of those would you like to join it with? Is there a logic behind this join?

Re: Join issue

rubenmarin — Mon, 18 Apr 2016 14:45:17 GMT

Hi anita, for the bills 1233, 1234.. wich row you want to keep and what are the conditions to choose one row over other?

Re: Join issue

Chanty4u — Mon, 18 Apr 2016 14:45:45 GMT

try

load

Distinct bill,

sum(credit_debit) as cd,

sum(discounts) as discounts1,

sum(_ev) as profit

Resident b

Group by bill;

left join (a)

load

* resident temp;

Re: Join issue

Mon, 18 Apr 2016 15:04:55 GMT

I found the solution. Thanks for your help though.

Re: Join issue

Chanty4u — Tue, 19 Apr 2016 05:17:14 GMT

cooool

Please close the thread by making correct and helpful answers above.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best

Chanty