topic Re: Expression to pick result based on MIN datetime in QlikView

Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 13:44:05 GMT

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.

Re: Expression to pick result based on MIN datetime

Clever_Anjos — Wed, 21 Sep 2016 13:47:44 GMT

Use FirstSortedValue() function

Re: Expression to pick result based on MIN datetime

Clever_Anjos — Wed, 21 Sep 2016 13:48:13 GMT

FirstSortedValue - chart function ‒ QlikView

Re: Expression to pick result based on MIN datetime

sunny_talwar — Wed, 21 Sep 2016 13:56:02 GMT

May be like this

Straight table

Dimension

ClientNumber

Expressions

TimeStamp(Min(ActualDateTime))

FirstSortedValue(SurveyQuery, ActualDateTime)

FirstSortedValue(Response, ActualDateTime)

Re: Expression to pick result based on MIN datetime

antoniotiman — Wed, 21 Sep 2016 14:47:37 GMT

Hi,

try

Dimension :

ClientNumber

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

SurveyQuery

Expression :

Min(Response)

Regards,

Antonio

Re: Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 15:00:58 GMT

@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.

Re: Expression to pick result based on MIN datetime

sunny_talwar — Wed, 21 Sep 2016 15:04:57 GMT

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)?

Re: Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 15:05:02 GMT

@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.

Re: Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 15:14:57 GMT

@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.

Re: Expression to pick result based on MIN datetime

sunny_talwar — Wed, 21 Sep 2016 15:17:52 GMT

Try this:

Dimension

ClientNumber

Expressions

TimeStamp(Min(ActualDateTime))

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

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

Re: Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 15:43:15 GMT

@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.

Re: Expression to pick result based on MIN datetime

sunny_talwar — Wed, 21 Sep 2016 15:45:39 GMT

How about may be this:

Dimension

ClientNumber

SurveyQuery

Expressions

TimeStamp(Min(ActualDateTime))

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

Re: Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 15:52:03 GMT

@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

Re: Expression to pick result based on MIN datetime

sunny_talwar — Wed, 21 Sep 2016 15:54:00 GMT

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

Re: Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 16:47:11 GMT

@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.

Re: Expression to pick result based on MIN datetime

sunny_talwar — Wed, 21 Sep 2016 16:52:12 GMT

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

Re: Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 17:03:05 GMT

@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.

Re: Expression to pick result based on MIN datetime

sunny_talwar — Wed, 21 Sep 2016 17:06:31 GMT

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)

Re: Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 17:09:57 GMT

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

Re: Expression to pick result based on MIN datetime

Anonymous — Wed, 21 Sep 2016 17:48:23 GMT

@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.