topic Re: Calculations through a date-bridge table! in QlikView

Calculations through a date-bridge table!

Wed, 22 Apr 2015 10:10:24 GMT

Hello QlikView friends,

I would like to ask a question regarding setting up formulas for a graph when using a date bridge table. Below there is the mapping I have made. It contains the Utilization table with information, attached to its own calendar. Throught a datebridge(link) connected to the canonical calendar. (I will connect more dates to the bridge later)

Script for this:

---------------

Utilization:

LOAD ac_registr,

operator,

daily_cycles,

daily_hours,

tac,

tah ;

SQL SELECT ac_registr,

departure_date,

daily_cycles,

daily_hours,

tac,

tah

FROM database;

Datebridge:

LOAD

ac_registr

,UtilizationDate as CanonicalDate

,'UtilizationDate' as DateType

RESIDENT Utilization;

------------------

Now here is the issue, I want to create graphs related to the CanonicalCalendar. The Utilization and Canonical Calendar should have the same values, but as you can see they differ when I set up the same graph. I am aware I might have to use a different expresion, Ive tried many such as:

Sum({<DateType={UtilizationDate}>}daily_cycles) Althought this gives the same result.

Does anyone have an idea as to what expression to use for the canonical graph to work properly? I has to look up the daily_cycles field somehow, is this even possible?

Re: Calculations through a date-bridge table!

marcus_malinow — Wed, 22 Apr 2015 10:17:30 GMT

Hi,

it looks like your values of ac_registr are related to CanonicalDates in both January and February.

If you post some sample data maybe we can help.

Marcus

Re: Calculations through a date-bridge table!

Wed, 22 Apr 2015 11:28:50 GMT

Hello thank you for offering help, I have attached a sample example in the main post!

Note: This is a very small sample, there are for example more ac_registr. But I kept it simple.

Re: Calculations through a date-bridge table!

marcus_malinow — Wed, 22 Apr 2015 11:38:54 GMT

Ok, well ac_registr is not date dependant. So, for one particular value, which relates to a number of utilisation dates, you have each record related to all canonical dates.

I'd suggest that ac_registr is not a good candidate here for your key field. Maybe in combination with utilisation date as a composite key it would be.

To do this:

At the start of your Utilization load change it to this

Utilization:
LOAD *,
ac_registr & '_' & UtilizationDate as %KeyACUtil;
LOAD * INLINE [ac_registr, UtilizationDate, daily_cycles,
TFA,1-1-2013,1
TFA,2-1-2013,1
TFA,4-1-2013,1

And change your Datebridge load to this:

Datebridge:

LOAD
ac_registr & '_' & UtilizationDate as %KeyACUtil
,UtilizationDate as CanonicalDate
,'UtilizationDate' as DateType
RESIDENT Utilization;

Re: Calculations through a date-bridge table!

Wed, 22 Apr 2015 13:24:14 GMT

hi Marcus that seems to work nicely, however this works if there is only one table with ac_registr. Otherwise loops will occur, which is an issue I have. I would have to rename ac_registr to ac_registr1 to prevent loops from happening.

In my full QVW I have 6 tables with ac_registr which all go to the datebridge, is there any way I could work my way arround this?

Re: Calculations through a date-bridge table!

marcus_malinow — Wed, 22 Apr 2015 13:38:53 GMT

Ok,

in that case

1 - add ac_registr to the Datbridge table load - in addition to %KeyACUtil

2 - after the Datebridge load, DROP FIELD ac_registr FROM Utilization

Re: Calculations through a date-bridge table!

Wed, 22 Apr 2015 14:00:44 GMT

You sir are my hero of the day! Thank you for your help! Greetings from the Netherlands

Re: Calculations through a date-bridge table!

marcus_malinow — Wed, 22 Apr 2015 14:04:40 GMT

Thanks, happy to help