Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner - Specialist
Partner - Specialist

Monthly filters through 2 databases?

Hello dear community, I am relatively new to QlikView and I still have a lot to learn.

I have data from 2 different databases ,
Database1 is with Service Requests
Database2 is with Sales

I have connected the 2 databases through Contract_ID ( its present in both databases)

My question is is it possible to make a filter which filters both databases .
Let me clarify

For example if i choose year 2017 - month January from Service requests

it will show me all values generated in that period

IS it possible to show me also all values generated in Sales database?

The field which i generate the Month and Year fields are different in both databases. But i want to make something like if i choose January 2017 from service requests, to show me also all sales in this period

Thanks in advance

-Angel

1 Solution

Accepted Solutions
tomovangel
Partner - Specialist
Partner - Specialist
Author

So guys i did it using Field event triggers

Document properties --> Triggers

I added it on select and on change

I just choose the field i want to duplicate selection ( lets name it YEAR1)

Then on add actions I choose Select In field : YEAR2


Search string : = '(' & getfieldselections(YEAR1,'|') & ')'


Hope this serves someone someday

Thanks all who replied to me

View solution in original post

8 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

You need either a LINK table or a CONCATENATED FACT

MASTERTABLE (LINK TABLE or Concatenated FACT Table)

arvind_patil
Partner - Specialist III
Partner - Specialist III

HI Angel,

Yes, you can do it with the help of master calendar just combine the two dates and create calendar .

Thanks,

Arvind Patil

tomovangel
Partner - Specialist
Partner - Specialist
Author

Could you give some example, because i watched some videos on youtube, and also saw a few posts about master calendar, but i don't know exactly how to combine them,

TransactDate and LaborDate are my fields...

effinty2112
Master
Master

Hi Angel,

You may have two difference data sources but after loading into QlikView all your data resides in your QV data model and you shouldn't think of filtering two different databases any more. It's difficult to give firm advice without knowing anything about your data but I think your first step should be to create two master calendars - one for sales dates and one for service request dates.

Sometimes this might be enough but because you want to look at sales data and service request data against a common date dimension you need to take a further step and construct a canonical calendar. If all this seems difficult here is the definitive guide to this topic:

Canonical Date

Good luck

Andrew

tomovangel
Partner - Specialist
Partner - Specialist
Author

Okay, thanks I am starting to read about canonical calendar


Thanks Andrew

-Angel

tomovangel
Partner - Specialist
Partner - Specialist
Author

So guys i did it using Field event triggers

Document properties --> Triggers

I added it on select and on change

I just choose the field i want to duplicate selection ( lets name it YEAR1)

Then on add actions I choose Select In field : YEAR2


Search string : = '(' & getfieldselections(YEAR1,'|') & ')'


Hope this serves someone someday

Thanks all who replied to me

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi Angel,

although this may work, it's probably not a good idea. Triggers rarely are, though granted they do on occasion serve a purpose.

Consider this scenario:

Sales

Contract ID    YEAR1

1              2015

2              2016

Service Requests

Contract ID    YEAR2

1              2015

1              2016

2              2016

2              2017

Now, what happens if your user selects 2016 in YEAR1?

QlikView will only associate the Sales in that year, then your tirigger will fire and further reduce the data in YEAR2. So your end product will be filtered to the second and third rows in Service Requests, however the second row will be filtered out based on YEAR1.

In this instance your filters will act as an AND condition rather than an OR. You will filter where Sales year is 2016 AND Service Requests year is 2016.

The solution to this is as stated before - model your data. Others have suggested a master calendar, which is a valid suggestion, however a first step towards this (in order to avoid a circular data model) is to set up either a concatenated fact of link table data model.

Hope this is of use,

Marcus

tomovangel
Partner - Specialist
Partner - Specialist
Author

Hello Marcus, I have given your post  a lot of thought, and I think it will be better to fix my data model,

Can you give me any reading for data modeling?

thanks in advance


-Angel