Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

Calculations through a date-bridge table!

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)

IssueTable.png

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?

IssueGraph.png

1 Solution

Accepted Solutions
marcus_malinow
Valued Contributor II

Re: Calculations through a date-bridge table!

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

7 Replies
marcus_malinow
Valued Contributor II

Re: Calculations through a date-bridge table!

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

Not applicable

Re: Calculations through a date-bridge table!

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.

marcus_malinow
Valued Contributor II

Re: Calculations through a date-bridge table!

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;

Not applicable

Re: Calculations through a date-bridge table!

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?

marcus_malinow
Valued Contributor II

Re: Calculations through a date-bridge table!

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

Not applicable

Re: Calculations through a date-bridge table!

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

marcus_malinow
Valued Contributor II

Re: Calculations through a date-bridge table!

Thanks, happy to help