topic Extract min time in QlikView

Extract min time

capriconuser — Mon, 21 Jan 2019 07:16:49 GMT

I have this kind of data

Product_Cate Datetime

2001 10:30:00 AM

2001 11:30:00 AM

2001 08:30:00 AM

20011 10:30:00 AM

20011 08:30:00 AM

20011 09:30:00 AM

NOW FROM here i am trying to extract min time and this should be like this

Product_Cate Datetime

2001 08:30:00 AM

20011 08:30:00 AM

Script

Load Product_Cate

DatimeTime

from abc_table

group by Product_Cate;

this shows an error

Aggregate function should be used in script when using group by

so how i extract min time and use group by in script?

Re: Extract min time

marcus_sommer — Mon, 21 Jan 2019 07:47:10 GMT

Try it in this way:

Load Product_Cate

time(min(DatimeTime)) as DatimeTime

from abc_table

group by Product_Cate;

- Marcus

Re: Extract min time

capriconuser — Mon, 21 Jan 2019 12:01:20 GMT

i have two tables like this

Load Product_Cate

time(min(DatimeTime)) as DatimeTime

from abc_table

inner join

load

code,

branch

from table2

group by code,branch,Product_Cate;

is this work ?

Re: Extract min time

marcus_sommer — Mon, 21 Jan 2019 12:07:15 GMT

It won't work in this way. You need at first to join the tables - whereby you need at least one KEY field which I don't see in your example - and within a following load you could aggregate your wanted measures over the available dimensions.

- Marcus

Re: Extract min time

capriconuser — Mon, 21 Jan 2019 12:20:03 GMT

i have the keys check this

Load
Codes1 as Key,
Product_Cate

time(min(DatimeTime)) as DatimeTime

from abc_table

inner join

load
Codes2 AS key,
code,

branch

from table2

group by code,branch,Product_Cate;

is this work ?

now how i use in that way group by and aggregate functio

Re: Extract min time

marcus_sommer — Mon, 21 Jan 2019 12:26:17 GMT

Try this:

table:
load Codes1 as Key, Product_Cate, DatetimeTime from abc_table;
inner join
load Codes2 AS Key, code, branch from table2;

final:
noconcatenate load code,branch,Product_Cate, time(min(DatetimeTime)) as DatetimeTime
resident table group by code,branch,Product_Cate;

drop tables table;

- Marcus