topic Re: three conditions in calculated dimension in QlikView

three conditions in calculated dimension

Fri, 18 Sep 2015 07:01:36 GMT

Hi there,

I need some help, please. I need to get a calculated dimension with three conditions. I have six streets (1-6) and two directions (up/down). I need to set up the dimension with street 3 & 5 combined but only in direction "up".

Can someone help me with it?

Thanks and regards

Benny

[EDIT] Sorry, I haven't been clear enough...

My streets are 1 to 6 and I want to see it like this:

Street 1, up: 30 cars

Street 2 up: 10 cars

Street 3 down: 50 cars

Street 3 + 1 down: 25 cars

The amount of cars is done in a formula (sum(cars)) but I need the right dimension... Something like

if(street='1' AND direction='up', street,

if(street='1' AND street='3' AND direction='up', street) )

and so on...

Does that makes sense? The problem is, if the if-condition is fulfilled in another line, the function stops which it shouldn't...

Re: three conditions in calculated dimension

pokassov — Fri, 18 Sep 2015 10:17:23 GMT

if((street=3 or street=5) and direction='up', '3&5 up')

Re: three conditions in calculated dimension

sunny_talwar — Fri, 18 Sep 2015 10:25:32 GMT

Would you be able to share some data and the expected output?

Re: three conditions in calculated dimension

Fri, 18 Sep 2015 12:45:24 GMT

updatet my post...

Re: three conditions in calculated dimension

pokassov — Fri, 18 Sep 2015 12:51:38 GMT

Street 1, up: 30 cars

Street 2 up: 10 cars

Street 3 down: 50 cars

Street 3 + 1 down: 25 cars

if(street<3,if(direction='up',street&' '&direction),

if(street=3,if(direction='down', street&' '&direction),

if(direction='down','street 3+ 1 '&direction)

)

and check supress when value is null

Re: three conditions in calculated dimension

Fri, 18 Sep 2015 12:55:43 GMT

Thank you Sergey

Street 1-6 is just an example. It is actually runways on an airport (14, 16, 28, 32, 34) so I need

Arrival 14

Arrival 16

Start 28

Start 28+16

Start 32

Start 32+34

So I guess your formula with street<3 won't work?!

Re: three conditions in calculated dimension

pokassov — Fri, 18 Sep 2015 13:12:49 GMT

Ok.

Can you explain the logic for direction. Which streets do you want to calculate with 'up' and which 'down'?

Re: three conditions in calculated dimension

Fri, 18 Sep 2015 13:20:30 GMT

OK, I wanted to use Streets to make it easy, but it doesn't work

I work for an airport and we want to see arrivals and departures on our runways.

The amount of movements is calculated by sum(ATM) (AirTrafficMovement)

Runways are the field RWY and departure or arrival is in field LSV (S=departure and L=Arrival)

Our runways are named 14, 28, 34, 16, 32

We need to see as follows:

Arrival 14 xxx

Arrival 28 xxx

Arrival 34 xxx

Departure 28 xxx

Departure 28&16 xxx (in sum)

Departure 32 xxx

Departure 32&34 xxx (in sum)

So the usual dimension would just be "RWY" but as I want to see it only with arrival or only with departure AND combined, I would use a calculated dimension...

Thank you very much ahead

Re: three conditions in calculated dimension

pokassov — Fri, 18 Sep 2015 13:43:37 GMT

Could you help me with one more logic point?

Departure 28&16 <- why you union these 2 runways?

Departure 32&34 <- why you union these 2 runways?

Re: three conditions in calculated dimension

Fri, 18 Sep 2015 13:44:34 GMT

LOL ask my boss, he wants to see it

Re: three conditions in calculated dimension

pokassov — Fri, 18 Sep 2015 15:07:33 GMT

From my point of view the best solution for your situation is changing model.

in case you want to calculate sum(ATM) for RWY=28 twice it's can be helpful.

1. add calculated field in your fact table during load:

fact:

load

...,

autonumber(RWY&'|'&LSV, 'Key RWY LSV') As %KeyRWYLSV

from...;

2. Add new table

RWY_LSV:

noconcatenate

load distinct

%KeyRWYLSV,

if(LSV='up', 'Departure '&RWY,'Arrival '&RWY) As CurrentStat

resident fact;

3. add linec with group fields

concatenate(RWY_LSV)

load distinct

%KeyRWYLSV,

if(LSV='up', 'Departure ','Arrival ')&if(match(RWY,28,16)>0,'28&' '&16',if(match(RWY,32,34)>0,'34&' '&32')) As CurrentStat

resident fact

where match(RWY,28,16,34,32)>0; //write there RWY which your boss want to summarize

then you can use CurrentStat as dimention.