Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be this
Sum(Aggr(
If(
Min(TOTAL <Surgeon, Year, Month, DOS> [Scheduled Start]) = [Scheduled Start] and
Min(TOTAL <Surgeon, Year, Month, DOS> [Scheduled Start]) < MakeTime(8, 31) and
Len(Trim(CaseOnTime)) > 0
, 1, 0)
, Year, Month, DOS, [Case ID], [Acct #], Surgeon, [Scheduled Start], [Actual Start], Diff))
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;
Thanks, I'll test this script and let you know.
It did not work...
It created duplicate rows with blanks...
Thanks a lot anyway.
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
You have a sample app? The one you tested with duplicated blank records?
I'll mock up an app for you...
5 mins.
Thanks
Create a straight table
Dimension:
Case,
Date,
Time,
Value
Expression: (Sales Person)
=aggr(FirstSortedValue(SalesPerson,Time),SalesPerson)
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.