Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
0422ogon
Partner
Partner

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
Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik

View solution in original post

9 Replies
aronwilliamson
Creator
Creator

Can you show how you have joined the date feilds

0422ogon
Partner
Partner
Author

Hi,

please see below:

Join.PNG

Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik
Michael_Tarallo
Employee
Employee

This may also help: Qlik Sense 3.0 - Creating a Composite / Compound / Custom Key

Regards,
Mike Tarallo
Qlik
JustinDallas
Specialist II
Specialist II

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
Partner
Partner

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

Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik
0422ogon
Partner
Partner
Author

thank you, that helped me a lot!

Michael_Tarallo
Employee
Employee

Awesome!

Regards,
Mike Tarallo
Qlik