9 Replies Latest reply: Nov 3, 2017 11:33 AM by Michael Tarallo

# Join two tables and use one dimension for filtering

Hi everyone,

i´m new to qlik sense and facing following topic:

I have two measures, both coming from different tables. After joining the two tables through Year and Month I want to use year and month as filtering dimensions in my report. I have following fields in my report:

TableA.Year, TableB.Year

TableA.Month, TableB.Month

TableA.Actuals

TableB.Forecast

When I use TableA. Year and TableA.Month, the figures from TableB.Forecast are not reacting. Only TableA.Actuals. What do I have to change in order to be able to filter with Year and Month both measures Actuals and Forecast?

Thank you in advance!

• ###### Re: Join two tables and use one dimension for filtering

Can you show how you have joined the date feilds

• ###### Re: Join two tables and use one dimension for filtering

Hello Onur - take a look at this document:

Generic keys

If I have a quick moment to build a sample I will post it.

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

• ###### Re: Join two tables and use one dimension for filtering

We need a little more structure as that will dictate the solution.  Does your example have two tables that are joined by a key?  Or have you LEFT JOINed two tables?

Try making a dummy example like so:

DemoTable:

[

TableA.Year ,TableA.Month, TableA.Actuals

2016, 10, 1000

2017, 8, 1244

]

;

Using that, we can figure out what direction to take you.

• ###### Re: Join two tables and use one dimension for filtering

Hi,

I would normally approach like the Below

FACT:

'Actuals'  AS Type,

Year,

Month,

Actuals  AS Amount

FROM TableA;

CONCATENATE

'Forecast'  AS Type,

Year,

Month,

Forecast AS Amount

FROM TableB;

You end up with one Fact Table and then both will react to the year and month

Set Analysis would be

SUM({<Type={'Actuals'}>}Amount)

SUM({<Type={'Forecast'}>}Amount)

Mark

• ###### Re: Join two tables and use one dimension for filtering

Try this sample - remember - Qlik Does not "JOIN" like a typical query-based SQL tool - we associate tables - based on same column names by default - or our data profiling will recommend  the best column match based on similar values in a column - you can also define you own - based on that video I sent you.

Also here is another video that shows some of the basics of creating a data model from multiple tables, In your case you have multiple fact tables - so it is a bit different.

New to Qlik Sense Videos

Creating a Data Model

If using Qlik Sense Desktop please copy .qvf file to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps and refresh Qlik Sense Desktop with F5. If using Qlik Sense Enterprise Server please import .qvf into your apps using the QMC - Qlik Management Console.

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Mike Tarallo (@mtarallo) | https://twitter.com/mtarallo

Qlik

• ###### Re: Join two tables and use one dimension for filtering

thank you, that helped me a lot!

• ###### Re: Join two tables and use one dimension for filtering

Awesome!