Indirect Set Analysis right tool for my task?

[benjamins]

Hello,

being quite new to Qlikview I have a problem which I'm currently trying to solve via indirect set analysis.

I'm not sure if this is the right tool to do so however.

I've got a table which looks (simplified) like this:

ProductId (number)

DepartureLocationName (string)

DepartureDelay (number)

ArrivalLocationName (string)

ArrivalDelay (number)

So the data displayed shows for a given product where it departed and with how much delay, as well as where it arrived and again with how much delay.

DepartureLocation and ArrivalLocation originate from the same master data Location table, which is also loaded in Qlikview and contains a value

LocationName (string).

What I'm now trying to do is the following:

Sum up delays per location

(based on other user choices, such as month, customer, etc.).

I'd have thought my function should have looked something like this:

Sum({$<ArrivalLocationName = P({$}LocationName)>} ArrivalDelay)

+ Sum({$<DepartureLocationName = P({$}LocationName)>} DepartureDelay)

But this does not seem to work out and produces strange results.

Any help would be greatly appreciated.

[swuehl]

Could you post a small sample file?

One idea might be to convert your table to a structure like

ProductID, LocationName, Delay, ArrivalOrDeparture

So you can just link your LocationName to your other table and you can just sum up Delays.

To convert your table, you can try making use of CROSSTABLE Load prefix (especially if you have a lot more fields per Arrival / Departure), or you can do two concatenated loads of your table, one time only loading the Departure fields (renamed), one time only the Arrival fields (plus ProductID and ArrivalOrDeparture field).

Something like

LOAD

ProductID, ArrivalLocationName as LocationName, ArrivalDelay as Delay, 'Arrival' as ArrivalOrDeparture

from Table;

LOAD

ProductID, DepartureLocationName as LocationName, DepartureDelay as Delay, 'Departure' as ArrivalOrDeparture

from Table;

Hope this helps,

Stefan

[benjamins]

Hi Stefan,

thanks a lot for your answer.

I compiled a quick and dirty sample to illustrate what I mean. I'm not sure if you can open it, as of now I'm using a personal edition.

http://www.file-upload.net/download-4800592/BStaehrSample.zip.html

I've already thought about a solution similar to yours as an ultima ratio using a view in SQL Server. Though as I understand it, I would be creating a data island by doing so?

I'll take a look at it!

[swuehl]

At the moment you are using a data island (for your location). You are getting totals, because your main table fields are not linked to your location table. You can also use a conditional to restrict results to the current dimension value:

=Sum(if(ArrivalLocationName = LocationName, ArrivalDelay))

+ Sum(if(DepartureLocationName = LocationName, DepartureDelay))

But performance will be poor for large tables.

I'll take a look into your input data now.

edit: here is a sample of my suggested solution, no data island and a simple sum(Delay) over dimension LocationName

LOCATION:

LOAD LocationId,

     LocationName

FROM

QV_LocationsSample.xlsx

(ooxml, embedded labels);

CUSTOMER:

LOAD ProductId,

     Customer,

     Month

FROM

QV_IndirectSetSample.xlsx

(ooxml, embedded labels);

DELAYS:

LOAD ProductId,

     DepartureLocationName as LocationName,

     DepartureDelay as Delay,

     'Departure' as Type

FROM

QV_IndirectSetSample.xlsx

(ooxml, embedded labels);

LOAD ProductId,

     ArrivalLocationName as LocationName,

     ArrivalDelay as Delay,

     'Arrival' as Type

FROM

QV_IndirectSetSample.xlsx

(ooxml, embedded labels);

[benjamins]

Thanks again!

For now I think I'll go with the conditional option, as I need to keep up the integrity of my original table for other qv objects and keep an eye on performance as the data warehouse grows larger.

Though if you got any suggestions how that can be achieved while upholding performance I'd be grateful!