Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

0422ogon
New Contributor II

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!

1 Solution

Accepted Solutions
Highlighted
Employee
Employee

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

9 Replies
aronwilliamson
Contributor

Re: Join two tables and use one dimension for filtering

Can you show how you have joined the date feilds

0422ogon
New Contributor II

Re: Join two tables and use one dimension for filtering

Hi,

please see below:

Join.PNG

Employee
Employee

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

Employee
Employee

Re: Join two tables and use one dimension for filtering

drillnaut
Valued Contributor

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:

LOAD * Inline

[

     TableA.Year ,TableA.Month, TableA.Actuals

      2016, 10, 1000

      2017, 8, 1244

]

;

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

mark6505
Valued Contributor III

Re: Join two tables and use one dimension for filtering

Hi,

I would normally approach like the Below

FACT:

LOAD

  'Actuals'  AS Type,

Year,

Month,

Actuals  AS Amount

FROM TableA;

CONCATENATE

LOAD

  '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

Highlighted
Employee
Employee

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

0422ogon
New Contributor II

Re: Join two tables and use one dimension for filtering

thank you, that helped me a lot!

Employee
Employee

Re: Join two tables and use one dimension for filtering

Awesome!