topic Re: Having Min(time) in QlikView

Having Min(time)

Wed, 27 Mar 2013 16:51:54 GMT

Hi,

I have to add a new flag field to a table

I got a table with

date,who,place,Time,key,Type.

I'd like to add 'like 'First_appointment'.' which could be true or false

It should mean:

In the day 13/01/2012 in the place A, which is the first appointment of that day in that place?

In oracle you should do smt like:

select a.date,a.where,a.key,1 as First_appointment

from TAB a

where not exists (select 1 from TAB b where a.date=b.date and a.place = b.place and b.time >a.time)

Do you think you can handle it?

many thanks

Re: Having Min(time)

Wed, 27 Mar 2013 17:14:36 GMT

You can not extract the data the way you need, as in the example?

Re: Having Min(time)

Gysbert_Wassenaar — Wed, 27 Mar 2013 17:43:22 GMT

Something like this

T1:

load autonumber(place&date&min(Time)) as Key

from ...somewhere...

group by place, date,Time;

left join

load autonumber(place&date&Time) as Key,

date,who,place,Time,key,Type

from ...somewhere...;

drop field Key;

Re: Having Min(time)

Thu, 28 Mar 2013 09:05:19 GMT

Hi,

see attached files.

Hope it helps

regards

Giampiero

Re: Having Min(time)

Wed, 03 Apr 2013 08:02:08 GMT

No, I can't use SQL, because the DB used as source is a view which has already undergone an ETL process.

Re: Having Min(time)

Fri, 05 Apr 2013 14:43:32 GMT

Without using aggr in the pivot.

Re: Having Min(time)

Fri, 05 Apr 2013 15:05:12 GMT

I cannot make it works properly, because with autonumber I've got different increasing number in T1 and in the left join (ex T1: 1,2,3 in the second table T2(if I cancel the left join, I've got 4,5,6).

Can you tell me where is my mistake?

Without the autonumber it works with place,date and min(time) used as multiple keys in T1