topic Re: Complex aggr with attached file in QlikView

Complex aggr with attached file

n1ef5ng1 — Tue, 22 Oct 2013 04:02:35 GMT

Have a look at the attached file for better understanding of the scenarios.

There is a table box and a pivot table (format is there). My desired layout will be on the pivot table where it hasa simple Year, Week and number of bus with special condition, this can be done by using Count(Bus)

as you can see from the table box, these are the buses that are being deploy on 2012 Week 2. Actual time of boarding is the time they left the paritcular bus_terminal. For first row, HAN LINN departed bus terminl call PPI1 at jan 10 2012.

In cases where bus have to move to another terminal due to shortage of buses over the other terminal, it will be call berthing sequence 2. Example will be bus TATA MACHAN, it first berth was at PPI1 and move to KI interchange which shows 2.

Therefore, I would want to count the number of buses that has move from terminal to another terminal.

The next condition is, if the bus move from within terminal saying SAN CALYPS where its berthing shift 1 and 2 is the same TPI, it should exclude from the counting of buses. This goes to TATA HADIAH where it shifted from PPI1 to PPI2 as PPI represent the same whole bus interchange.

I would want to count bus such as JINYUNHE where it move from KI to PPI1, these are count as one bus that really deploy to other terminal.

Is there anyway I can do in a single expression?

Correct result will be 3 which is JINYUNHE, TATA LAMBANG and TATA MACHAN

Re: Complex aggr with attached file

Tue, 22 Oct 2013 04:32:59 GMT

Hi Benn,

Some calculation in script will make this expression easier. please refer to attached application which i modified from your original application. hope it helps

Re: Complex aggr with attached file

n1ef5ng1 — Tue, 22 Oct 2013 04:45:41 GMT

possible to do these in expression as I am unable to edit anything from the script

Re: Complex aggr with attached file

Tue, 22 Oct 2013 06:05:34 GMT

Here is the expression you want

Count

(DISTINCT Aggr(If(Left(MaxString(Bus_Terminal_ID),2)<>(Left(MinString(Bus_Terminal_ID),2)),Bus_Name),Bus_Name))

Re: Complex aggr with attached file

n1ef5ng1 — Tue, 22 Oct 2013 06:45:58 GMT

Thanks, is getting there. But i need to check in berthing sequence as if this identical bus will to have many rows with one berthing sequence without changing any terminal, it will still take in as one count as per ur code

Re: Complex aggr with attached file

Tue, 22 Oct 2013 07:16:52 GMT

This will work

Sum(Aggr(Count(DISTINCT Left(Bus_Terminal_ID,2))-1,Bus_Name))

Re: Complex aggr with attached file

n1ef5ng1 — Tue, 22 Oct 2013 07:27:26 GMT

This code works!

Count

(DISTINCT Aggr(If((Left(MaxString(Bus_Terminal_Id),2) <>(Left(MinString(Bus_Terminal_Id),2)))
and Left(MaxString(Berthing_Seqence),1) <> Left(MinString(Berthing_Seqence),1)
,Bus_Name),Bus_Name,Week,Year))
My last issue is, hope you can help

If bus terminal will to move between KI and TI , it will not counted as one. This is like PPI1 and PPI2. This is tricky

Re: Complex aggr with attached file

n1ef5ng1 — Tue, 22 Oct 2013 07:39:44 GMT

Is there additional code that I can add in the the code above saying that if bus terminal move between KI and TI, do not count as one

Re: Complex aggr with attached file

Tue, 22 Oct 2013 08:49:59 GMT

Hi benn,

Your expression is getting too complex, it is advisable if you get your script modified and handle few scenarios in your script.

Re: Complex aggr with attached file

n1ef5ng1 — Wed, 23 Oct 2013 00:47:26 GMT

But my expression is nearly there, just need to add in one more condition to