Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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.
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;
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?
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
You sir are my hero of the day! Thank you for your help! Greetings from the Netherlands
Thanks, happy to help