Central point for selections on several tables

[datanibbler]

Hi,

I have several KPIs (types of costs) and several tables in the QlikView_database (one for every KPI).

They are all linked to a master_calendar via a field labeled > %Datum < so that I can use fields from that calendar for the dimensions.

They also all have a field labeled > plant < - well, the name is of course different in each table, I had to give them all differing names to avoid synthetic keys.

<=> Now I want to include another sheet with all the costs added up per plant - so I need a central "point of selection" which will select on all the plant_fields.

=> In principle, it's clear how that has to be done: I have to create several doc_level_triggers, using the drop_down_menu "field_level".

There seems to be some issue between the tables, however: The selection seems to be incompatible between some tables - I don't understand how that can be? Of course, there might be no data in one or more of the tables for a certain selection, but how can that be incompatible?

Now what? Do I have to disconnect one of the tables from the master_calendar? Of course, I could do that, it's just nicer that way.

Can anybody assist me there?

Thanks a lot!

Best regards,

DataNibbler

[martinpohl]

Hello DataNibbler,

you can use two types of datamodel to find what you are looking for.

1.: Conactenate all costs into one table. Same fields like Datum and Plant (and Customer, Material etc) and different facts fields like order costs, invoice costs, strage costs etc

2.: Buld links tables to your fact tables. They need a new link field in your example Datum&Plant but all other fields where your want to compare the values from. In this link table there are the selectable fields like Datum and Plant.

I think the problems in your data model now is that there are values on one plant on selected dates, but on other days in the second table.

Concatenate creates one big fact table but you can choose all your dimensions (that are linked with the datas), my favorite.

Regards

[datanibbler]

Hello Martin,

thanks for the answer!

A link table would be a possible way to do this.

However, there also seems to be an issue with that field in one of the tables. I'll try to fix that first and then I'll see.

Best regards,

DataNibbler

[datanibbler]

Yippieh!

Now it works.

I have not built a link_table, simply created some doc_level_triggers to do the selections on all tables, based on a small inline_table.

Thanks a lot!

[datanibbler]

There is just one point:

In one of the tables, there is no date_field, there is just a month.

The problem ábout the selections seems to be that I have created a date_field which is always the 1st day of a month.

=> Now, when I select the year 2014, because of that table, only the 1st day of each month is available, so in some of the other tables, no selection is possible.

I will try to amend that by connecting that table by the month_field instead.

[datanibbler]

Hi,

now there seems to be another issue rgd. the selections:

- I now have 4 tables, each one has a date_field.

- 3 are connected to the master_calendar via that date_field.

- The 4th does not have a date or data for any day, it just has one value per month, so it is connected to the
   master_calendar via the month

=> When I select the year 2014 from the master_calendar AND I select one of the plants in any of the 4 tables, then
     all months (10 or 11) are available.

<=> When I select the year 2014 and that same plant from the central selection_table, only 2 months are available

=> How should I best proceed now to find out what exactly is the cause of this issue?

Best regards,

DataNibbler

[datanibbler]

Hi,

I'm still at a loss about how I could solve this issue:

=> The root_cause of my issues seems to be that for specific combinations of the year and the plant,

       there is simply no data in the tables

       That is no big issue in itself.

<=> When I combine the selection of

   - a year in the master_calendar

´  - a plant in both of the tables

=> Some months are not available as there is data in only one of the tables

=> This issue naturally gets worse if I add more tables as no table has data for every day

=> How can I solve this (if, in any of the tables, there is no data for the timeframe specified in my central
      selection_table, the month should still be available, only without cost_data from that specific table)?

Does anyone have an idea how that might be done?

Thanks a lot!

Best regards,

DataNibbler

P.S.: It seems clear that I have to somehow create a "pumped_up" table where there data for every single day - the value added from all tables where there is any and a 0 added from tables where there is none ... but how to do that?