Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

Extract min time

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?

 

5 Replies
marcus_sommer

Try it in this way:

Load Product_Cate

time(min(DatimeTime)) as DatimeTime

from abc_table

group by Product_Cate;

- Marcus

capriconuser
Creator
Creator
Author

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 ?

marcus_sommer

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

capriconuser
Creator
Creator
Author

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
marcus_sommer

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