Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Having Min(time)

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

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Having Min(time)

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;


talk is cheap, supply exceeds demand
6 Replies
Not applicable

Re: Having Min(time)

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

MVP & Luminary
MVP & Luminary

Re: Having Min(time)

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Having Min(time)

Hi,

see attached files.

Hope it helps

regards

Giampiero

Not applicable

Re: Having Min(time)

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

Not applicable

Re: Having Min(time)

Without using aggr in the pivot.

Not applicable

Re: Having Min(time)

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

Community Browser