Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ndeeleysww
Contributor

Join tables but not duplicate data..

Hi,

Apologies if the title of this question doesn't make sense - I don't know how to describe it!

I have two tables. Table A shows a list of sites, a Month, and a budget for that month:

A.SiteA.MonthA.Budget
My PlaceJan1000
My PlaceFeb2000
My SiteJan1000

The budget shows the total sum that site is allowed to spend.

I need to join this in Sense to Table B, which shows what actually has been spent, by item:

JanJan40.00
B.SiteB.MonthB.ItemB.Amount
My PlaceGloves30.00
My SiteShoes

I've tried joining the two tables using every join method I can think of, but when I do the Budget column is duplicated, and therefore the total sum of that budget is incorrect in the app:

A.SiteA.MonthB.ItemB.AmountA.Budget
My PlaceJanGloves301000
My PlaceJanShoes401000
My PlaceJanGloves201000
TOTAL903000

How do I join the tables so that the Amount is Totalled by the Budget is shown only once?

Hope that makes Sense!

Tags (3)
4 Replies

Re: Join tables but not duplicate data..

Rather than join use Concatenate

ndeeleysww
Contributor

Re: Join tables but not duplicate data..

Thanks Manish - I'll give it a try.

ndeeleysww
Contributor

Re: Join tables but not duplicate data..

I did try this, but although it worked, it wasn't as flexible as I needed. The data was appended to the end of my query and not associated with the columns I needed, so I couldn't filter properly.

What I did in  the end was ran a query against my two tables, joining what was needed, then used this as a basis for a sql union, applying null fields to columns that did not exist. It was a hassle, but I got round it...

phaneendra_kunc
Valued Contributor III

Re: Join tables but not duplicate data..

Why cant you keep these two tables separate and create a Concatenated Key? this will ensure you wont have duplicates.

Like Site&'-'&Month as Key