Expression to pick result based on MIN datetime

[Anonymous]

Hello,

I have the below information on one customer. Since I have so many database tables joined, I am avoiding to modify my sql script. Therefore, I needed help on writing an expression to pick minimum response where ActualDateTime is minimum.

ClientNumber	Template	ActualDateTime	Assessment	SurveyQuery	Response
C0001	Arrival	2015-08-23 01:10:00.000	1	SurveyStat	2
C0001	Arrival	2015-08-23 01:10:00.000	1.01	SurveyStat	2
C0001	Arrival	2015-08-23 01:10:00.000	1.02	SurveyStat	3
C0001	Departure	2015-08-23 01:25:00.000	1	SurveyStat	2
C0001	Departure	2015-08-23 01:25:00.000	1.01	SurveyStat	2
C0001	Departure	2015-08-23 01:25:00.000	1.02	SurveyStat	3

Expected Result:

ClientNumber	ActualDateTime	SurveyQuery	Response
C0001	2015-08-23 01:10:00.000	SurveyStat	2

What will be the right expression to get the expected result?

Thank you.

[Clever_Anjos]

Use FirstSortedValue() function

[Clever_Anjos]

FirstSortedValue - chart function ‒ QlikView

[sunny_talwar]

May be like this

Straight table

Dimension

ClientNumber

Expressions

TimeStamp(Min(ActualDateTime))

FirstSortedValue(SurveyQuery, ActualDateTime)

FirstSortedValue(Response, ActualDateTime)

[antoniotiman]

Hi,

try

Dimension :

ClientNumber

Aggr(MinString(ActualDateTime),ClientNumber)  (Check 'Suppress when Null value')

SurveyQuery

Expression :

Min(Response)

Regards,

Antonio

[Anonymous]

@Sunny! TimeStamp(Min(ActualDateTime)) gives me the right value. However, FirstSortedValue(SurveyQuery, ActualDateTime) is still giving me blank. It might be something to do on how my tables are joined.

[sunny_talwar]

Vishal -

I guess you have three rows with the same TimeStamp, how did you decide to pick 2 here and which 2 (Row 1 or Row 2)?

[Anonymous]

@Antonio! Response does not have to be minimum. I need to pick any response based on the minimum ActualDateTime. Your logic though of adding the dimension field will be helpful.

[Anonymous]

@Sunny!

Good catch. I updated my original post. I added "Assessment" column. In my sql it only picks records where Assessment value is 1. When I added a listbox to see what ActualDateTime are available, I see 2015-08-23 01:10:00.000 and 2015-08-23 01:25:00.000. So TimeStamp(Min(ActualDateTime)) gives me 2015-08-23 01:10:00.000. However, FirstSortedValue(SurveyQuery, ActualDateTime) results blank.

Sorry.

[sunny_talwar]

Try this:

Dimension

ClientNumber

Expressions

TimeStamp(Min(ActualDateTime))

FirstSortedValue(DISTINCT {<Assessment = {1}>}SurveyQuery, ActualDateTime)

FirstSortedValue(DISTINCT {<Assessment = {1}>}Response, ActualDateTime)