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.

[Anonymous]

@Sunny! Great. That worked. Just little bit more complication now based on the modification request. I need data on 2 SurveryQuery. Initially it was only SurveyStat but now we need information from 2 queries. First query is SurveyStat and second is SurveyStat2. How can I differentiate the responses for different queries?

Thanks so much.

[sunny_talwar]

How about may be this:

Dimension

ClientNumber

SurveyQuery

Expressions

TimeStamp(Min(ActualDateTime))

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

[Anonymous]

@Sunny! But don't we have to define where SurveyQuery=SurveyStat and SurveyQuery=SurveyStat2. Because results for different queries needs to be populated in different columns. I hope you are understanding. I can play around now though. I am getting the idea of your logic. Heading for lunch. ttyl

[sunny_talwar]

From what I think I understand, it seems that you are looking for what I have provided above. But I think you will have to test it out and let us know if this is indeed working or not?

Best,

Sunny

[Anonymous]

@Sunny! I tested it out. It perfectly worked fine when SurveyQuery='SurveyStat'. Now, in sql script I have
WHERE SurveyQuery IN ('SurveyStat','SurveyStat2') . So I need:

Expression:

TimeStamp(Min(ActualDateTime)) where SurveyQuery='SurveyStat'

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

FirstSortedValue(DISTINCT {<Assessment = {1}>}Response, ActualDateTime) where SurveyQuery='SurveyStat'

TimeStamp(Min(ActualDateTime)) where SurveyQuery='SurveyStat2'

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

FirstSortedValue(DISTINCT {<Assessment = {1}>}Response, ActualDateTime) where SurveyQuery='SurveyStat2'

Thanks.

[sunny_talwar]

Vishal are you trying this out in the script or front end chart?

[Anonymous]

@Sunny! Sorry for the confusion. Let me clarify. In my sql script, I have WHERE Assessment='1' and SurveyQuery IN ('SurveyStat','SurveyStat2'). (I believe we don't even need DISTINCT {<Assessment = {1}>} in expression.) Everything else needs to be done on the front end chart.

Below is what I need to fianlly accomplish:

Dimension

ClientNumber

Expression:

TimeStamp(Min(ActualDateTime)) where SurveyQuery='SurveyStat'

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

FirstSortedValue(DISTINCT {<Assessment = {1}>}Response, ActualDateTime) where SurveyQuery='SurveyStat'

TimeStamp(Min(ActualDateTime)) where SurveyQuery='SurveyStat2'

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

FirstSortedValue(DISTINCT {<Assessment = {1}>}Response, ActualDateTime) where SurveyQuery='SurveyStat2'

Thank you.

[sunny_talwar · Accepted solution]

May be this:

Expression:

TimeStamp(Min({<SurveyQuery={'SurveyStat'}>}ActualDateTime))

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

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

TimeStamp(Min({<SurveyQuery={'SurveyStat2'}>ActualDateTime))

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

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

[Anonymous]

This is exactly what I need. Let me test it.

[Anonymous]

@Sunny! That did the trick. I just had to edit few punctuation on the second query expressions on your response.

TimeStamp(Min({<SurveyQuery={'SurveyStat'}>}ActualDateTime))

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

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

TimeStamp(Min({<SurveyQuery={'SurveyStat2'}>}ActualDateTime))

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

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

Thanks so much.