Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

MK9885
Honored Contributor II

Show only earliest value in a day

Hi,

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

Ex: 

CaseValueTimeDateSalesPerson
1ABC6:00 AM1/1/2019Mary
2BCD6:30 AM1/1/2019Mary
3CDE8:50 AM2/2/2019Mary
4EFG10:00 AM9/15/2019John
5HIJ12:00 PM3/22/2019Doug
6KLM7:30 AM5/5/2019Andrew
7NOP7:15 AM5/5/2019Andrew

 

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.

Labels (1)
1 Solution

Accepted Solutions
MK9885
Honored Contributor II

Re: Show only earliest value in a day

Correction ***

I used your expression @ Kush. Though the counts were coming correct, it wasn't tagging correct CASE ID's

So Instead I used below expression

Count(If(Aggr(NODISTINCT Min([Sched Start]), CaseOnTime,DOSMonth) = [Sched Start], CaseOnTime))

Also changed my calculated dimension in back end script for one of the main dimensions-CaseOnTime.

Thanks a lot Kush and also thanks a lot to Arthur for helping me sort this out 🙂

View solution in original post

28 Replies
Partner
Partner

Re: Show only earliest value in a day

Try this:

MC.PNG

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
Honored Contributor II

Re: Show only earliest value in a day

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

MK9885
Honored Contributor II

Re: Show only earliest value in a day

It did not work...

It created duplicate rows with blanks...

Thanks a lot anyway.

Support
Support

Re: Show only earliest value in a day

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
MK9885
Honored Contributor II

Re: Show only earliest value in a day

Anyone who can help me with this?

@sunny_talwar or anyone?

 

Thanks.

Partner
Partner

Re: Show only earliest value in a day

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

MK9885
Honored Contributor II

Re: Show only earliest value in a day

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

5 mins.

Thanks

Re: Show only earliest value in a day

Create a straight table

Dimension:

Case,

Date,

Time,

Value

Expression: (Sales Person)

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

MK9885
Honored Contributor II

Re: Show only earliest value in a day

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.