topic Re: Monthly filters through 2 databases? in QlikView

Monthly filters through 2 databases?

tomovangel — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Monthly filters through 2 databases?

marcus_malinow — Wed, 26 Jul 2017 13:36:56 GMT

You need either a LINK table or a CONCATENATED FACT

MASTERTABLE (LINK TABLE or Concatenated FACT Table)

Re: Monthly filters through 2 databases?

arvind_patil — Wed, 26 Jul 2017 13:38:35 GMT

HI Angel,

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

Thanks,

Arvind Patil

Re: Monthly filters through 2 databases?

tomovangel — Thu, 27 Jul 2017 06:17:34 GMT

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

Re: Monthly filters through 2 databases?

effinty2112 — Thu, 27 Jul 2017 07:08:14 GMT

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

Re: Monthly filters through 2 databases?

tomovangel — Thu, 27 Jul 2017 07:23:37 GMT

Okay, thanks I am starting to read about canonical calendar

Thanks Andrew

-Angel

Re: Monthly filters through 2 databases?

tomovangel — Fri, 28 Jul 2017 08:57:59 GMT

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

Re: Monthly filters through 2 databases?

marcus_malinow — Fri, 28 Jul 2017 09:44:18 GMT

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

Re: Monthly filters through 2 databases?

tomovangel — Mon, 31 Jul 2017 11:31:49 GMT

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