topic Re: Help with an expression in QlikView

Help with an expression

rustyfishbones — Mon, 25 Jun 2018 15:46:45 GMT

Hi All,

I have the following expression.

ONLY({<[date-added] = {'$(=DATE(MAX([date-added])))'}>}TaskStartTime)

the expression returns the following data, I only want the first time for each operator for that day, see in bold.

TaskDescription	TaskOperator	TaskEndTime	Date	ONLY({<[date-added] = {'25/06/2018'}>}TaskStartTime)
Picking	Hyfb Qiheepzq	10:04:00	25/06/2018	08:33:00
BREAK/LUNCH	Hyfb Qiheepzq	10:15:00	25/06/2018	10:04:00
Picking	Hyfb Qiheepzq	13:04:00	25/06/2018	10:15:00
BREAK/LUNCH	Hyfb Qiheepzq	13:38:00	25/06/2018	13:04:00
Picking	Hyfb Qiheepzq	15:12:00	25/06/2018	13:38:00
BREAK/LUNCH	Hyfb Qiheepzq	15:23:00	25/06/2018	15:12:00
Picking	Hyfb Qiheepzq	15:23:00	25/06/2018	15:23:00
Picking	Jfkee Inpqdc	10:05:00	25/06/2018	07:31:00
BREAK/LUNCH	Jfkee Inpqdc	10:20:00	25/06/2018	10:05:00
Picking	Jfkee Inpqdc	12:39:00	25/06/2018	10:20:00
BREAK/LUNCH	Jfkee Inpqdc	13:18:00	25/06/2018	12:39:00
Picking	Jfkee Inpqdc	13:18:00	25/06/2018	13:18:00

Re: Help with an expression

sunny_talwar — Mon, 25 Jun 2018 15:51:02 GMT

May be this

If(TaskStartTime = Min(TOTAL <TaskOperator> {<[date-added] = {'25/06/2018'}>}TaskStartTime), TaskStartTime)

Re: Help with an expression

rustyfishbones — Mon, 25 Jun 2018 15:59:09 GMT

Thanks Sunny, but that does not give me the correct data, it close but not correct

Re: Help with an expression

sunny_talwar — Mon, 25 Jun 2018 16:01:24 GMT

What is the issue still?

Re: Help with an expression

rustyfishbones — Mon, 25 Jun 2018 16:04:01 GMT

Ok I think I have it, the table contained additional dimensions the below seems correct.

If(TaskStartTime = Min(TOTAL <TaskOperator,[date-added],TaskDescription> {<[date-added] = {'$(=DATE(MAX([date-added])))'}>}TaskStartTime), TaskStartTime)

Re: Help with an expression

rustyfishbones — Mon, 25 Jun 2018 16:04:45 GMT

Actually thats not quite correct..... mmm...

Re: Help with an expression

rustyfishbones — Mon, 25 Jun 2018 16:06:35 GMT

I am missing some records

Re: Help with an expression

sunny_talwar — Mon, 25 Jun 2018 16:09:37 GMT

I am not sure what is wrong by looking at the image... can you elaborate what is missing?

Re: Help with an expression

captain89 — Mon, 25 Jun 2018 16:13:11 GMT

Hi,

you can use the min expression with aggr in this case:

aggr(min({<[date-added]={'26/06/2018'}>} HH), TaskOperator, [date-added])

maybe if you delete the set analysis part you can show the min of a selected date:

aggr(min(HH), TaskOperator, [date-added])

Re: Help with an expression

rustyfishbones — Mon, 25 Jun 2018 16:25:41 GMT

Hi Sunny see below image i need to see 06:27:00 as the TaskStartTime, with your expression I get no records when I select a TaskOperator

Re: Help with an expression

sunny_talwar — Mon, 25 Jun 2018 16:31:14 GMT

Can you add this expression to the bottom table and share a screenshot? My guess is that it is related to rounding issues....

Min(TOTAL <TaskOperator,[date-added],TaskDescription> {<[date-added] = {'$(=DATE(MAX([date-added])))'}>}TaskStartTime)

If this is rounding issue.. may be you can try this

If(Round(TaskStartTime*86400) = Round(Min(TOTAL <TaskOperator,[date-added],TaskDescription> {<[date-added] = {'$(=DATE(MAX([date-added])))'}>}TaskStartTime) * 86400), TaskStartTime)

Re: Help with an expression

rustyfishbones — Mon, 25 Jun 2018 16:31:59 GMT

close enough, this is the correct expression.

TIME(aggr(min({<[date-added]={'$(=DATE(MAX([date-added])))'}>} TaskStartTime), TaskOperator, [date-added]),'hh:mm')

Thanks