Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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 III

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 III

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 III

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 III

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 III

Re: Calculations through a date-bridge table!

Thanks, happy to help

Community Browser