Show only earliest value in a day

[MK9885]

Hi,

How do I show only an earliest value in a selected day..

Ex: 

Case	Value	Time	Date	SalesPerson
1	ABC	6:00 AM	1/1/2019	Mary
2	BCD	6:30 AM	1/1/2019	Mary
3	CDE	8:50 AM	2/2/2019	Mary
4	EFG	10:00 AM	9/15/2019	John
5	HIJ	12:00 PM	3/22/2019	Doug
6	KLM	7:30 AM	5/5/2019	Andrew
7	NOP	7:15 AM	5/5/2019	Andrew

I'd like to show/count Case 1 for Mary for date 01/01/2019 cus that's the earliest case (with time stamp) for her that day and not count case 2 or case 3.

Same for Sales Person Andrew

Would like to count only Case # 7 as that was done 07:15 AM

I tried Peek and also Firstvalue

NoConcatenate

Data:

Load Case ,

FirstValue(Value) as Value

Resident Dim Group by Case;

This gives me the answer but in expression it's getting messed up.

Thanks.

[Arthur_Fong]

Try this:

Script:

Raw:
load * inline [
Case	Value	Time	Date	SalesPerson
1	ABC	6:00 AM	1/1/2019	Mary
2	BCD	6:30 AM	1/1/2019	Mary
3	CDE	8:50 AM	2/2/2019	Mary
4	EFG	10:00 AM	9/15/2019	John
5	HIJ	12:00 PM	3/22/2019	Doug
6	KLM	7:30 AM	5/5/2019	Andrew
7	NOP	7:15 AM	5/5/2019	Andrew
](delimiter is '	');

NoConcatenate
Data:
load
if(RowNo()=1,1,if(SalesPerson=peek(SalesPerson),peek(Flag)+1,1))as Flag,
Case,Value,Time#(Time,'h:mm tt') as Time,Date#(Date,'MM/DD/YYYY')AS Date,SalesPerson
resident Raw
order by SalesPerson,
		 Date,
         Time;
drop table Raw;

NoConcatenate
Data2:
load * resident Data 
where Flag=1;
drop table Data;

exit script;

[MK9885]

Thanks, I'll test this script and let you know.

[MK9885]

It did not work...

It created duplicate rows with blanks...

Thanks a lot anyway.

[Brett_Bleess]

MK, did you want further help on things, your last post may have confused folks, so I just wanted to suggest following up if you did want further ideas.  The only other thing I can offer is the Design Blog area link, we have hundreds of how-to posts out there, do not know if there is anything specific on what you are trying to do here, but I will let you search yourself on this one given I am not quite sure which one(s) may be most applicable.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[MK9885]

Anyone who can help me with this?

@sunny_talwar or anyone?

Thanks.

[Arthur_Fong]

You have a sample app? The one you tested with duplicated blank records?

[MK9885]

I'll mock up an app for you...

5 mins.

Thanks

[Kushal_Chawda]

Create a straight table

Dimension:

Case,

Date,

Time,

Value

Expression: (Sales Person)

=aggr(FirstSortedValue(SalesPerson,Time),SalesPerson)

[MK9885]

Please see attached file.

I'm looking at 10/1/2018 has 2 CASE ID for same day

One starts at 07:30

another at 08:15

So i;d like to only see 07:30 when I select year 2018, month Oct and Surgeon.

For security reason, I've scrambled Surgeon names.