topic Re: Help required regarding select in field in QlikView

Help required regarding select in field

Anonymous — Mon, 30 May 2016 09:59:34 GMT

Hi All

i calculated the response time for opening a dashboard in access point , audit timestamp - session start , which is giving me the result , i took this this expression with different aggregations like max,min,avg .

Now the requirement is when i click on the Max text box it should show me the session is associated with it , so what i did is i took select in field in actions and field as session and i have given the same expression there but it is not working , please help kkkumar82‌gwassenaar‌swuehl‌kush141087‌mby‌mrkachhiaimp‌maxgro‌rwunderlich‌Robert_Mika‌AndreeBauer‌

Re: Help required regarding select in field

sunny_talwar — Mon, 30 May 2016 10:02:23 GMT

You may not be able to equate timestamp due to rounding errors and may need to use greater then equal to and less than equal to conditions. Would you be able to share a sample to see what is going on?

Re: Help required regarding select in field

Anonymous — Mon, 30 May 2016 10:17:25 GMT

cant share , its confidential

here is the expressions :

=max(Second(Frac(([Audit Timestamp]) - [Session Start]))) - i took this expression which is giving me the value of 54, so when i click on this text box i want the data get filtered and show me in which session it took 54 seconds to load

Re: Help required regarding select in field

sunny_talwar — Mon, 30 May 2016 10:21:54 GMT

How about this:

='>' & Max(Second(Frac(([Audit Timestamp]) - [Session Start])), 2) & '<' & (Max(Second(Frac(([Audit Timestamp]) - [Session Start]))) + (1/86400))

Re: Help required regarding select in field

Anonymous — Mon, 30 May 2016 10:26:47 GMT

Nope , nothing is getting selected

Re: Help required regarding select in field

sunny_talwar — Mon, 30 May 2016 10:28:59 GMT

How about this:

Max(TOTAL Second(Frac(([Audit Timestamp]) - [Session Start])))

='>' & Max(TOTAL Second(Frac(([Audit Timestamp]) - [Session Start])), 2) & '<' & (Max(TOTAL Second(Frac(([Audit Timestamp]) - [Session Start]))) + (1/86400))

Re: Help required regarding select in field

Anonymous — Mon, 30 May 2016 10:31:30 GMT

Here is the Script i am using :

LOAD //[Server Started],

Timestamp as [Audit Timestamp],

//Document,

Type,

User ,

Message,

Id as AuditID ,

Session

LOAD [Exe Type],

[Exe Version],

[Server Started],

Timestamp,

Document,

[Document Timestamp],

[QlikView User],

[Exit Reason],

[Session Start],

[Session Duration],

[CPU spent (s)],

[Bytes Received],

[Bytes Sent],

Calls,

Selections,

[Authenticated user],

[Identifying user],

[Client machine identification],

[Serial number],

[Client Type],

[Client Build Version],

[Secure Protocol],

[Tunnel Protocol],

[Server Port],

[Client Address],

[Client Port],

[Cal Type],

[Cal Usage Count],

Session

Temp:

load

[Authenticated user],

[Session Start]

resident Session;

left join (Temp)

load

User,

[Audit Timestamp]

resident Audit;

Data:

load

User,

[Session Start],

[Audit Timestamp],

Second(Frac(([Audit Timestamp]) - [Session Start])) as Response

resident Temp;

DROP Table Temp;

Re: Help required regarding select in field

sunny_talwar — Mon, 30 May 2016 10:34:18 GMT

Did you try with TOTAL? Did not work?

Re: Help required regarding select in field

Anonymous — Mon, 30 May 2016 10:43:26 GMT

i tried total it didnt work ,here are the screenshots for better understanding

Re: Help required regarding select in field

sunny_talwar — Mon, 30 May 2016 10:52:47 GMT

Is Session a number field or time field in seconds? You can check this by creating a list box with this expression -> Num(Session). If it is showing decimal numbers then it is in seconds, else it is number. If it is number, try this:

=Max(Num#(Second(Frac(([Audit Timestamp]) - [Session Start])), '##'))

Re: Help required regarding select in field

Anonymous — Mon, 30 May 2016 11:18:16 GMT

Please find the attached QVW

Re: Help required regarding select in field

sunny_talwar — Mon, 30 May 2016 11:23:13 GMT

So you basically want to be able to select 1576?

Try this ->

=Concat(DISTINCT {<[Response Time] = {"$(=Max([Response Time]))"}>} Session)

=Concat(DISTINCT {1<[Response Time] = {"$(=Max([Response Time]))"}>} Session)

Re: Help required regarding select in field

Anonymous — Mon, 30 May 2016 11:25:15 GMT

Awesome !!!!! Cheers bro

i am looking forward to connect with you

Re: Help required regarding select in field

Anonymous — Mon, 30 May 2016 11:34:14 GMT

Somehow this is not working for Min

Re: Help required regarding select in field

sunny_talwar — Mon, 30 May 2016 11:40:40 GMT

Try now

Min -> ='(' & Concat(DISTINCT {1<[Response Time] = {"$(=Min([Response Time]))"}>} Session, '|') & ')'

Max -> ='(' & Concat(DISTINCT {1<[Response Time] = {"$(=Max([Response Time]))"}>} Session, '|') & ')'

Re: Help required regarding select in field

Anonymous — Mon, 30 May 2016 11:40:40 GMT

Somehow this is not working for Min

Re: Help required regarding select in field

sunny_talwar — Mon, 30 May 2016 11:42:03 GMT

Look at my response above. I have attached the qvw also to my response. See if that helps

Re: Help required regarding select in field

Anonymous — Mon, 06 Jun 2016 05:00:52 GMT

sorry for the ad hoc request . i am trying to build a master calendar but failing as the time stamp format in the sever and the calendar which i am trying to build is in different formats , i request you to help me to build a master calender so that i can extract day ,time , hour , week , month,month name and all possible fields from it

Re: Help required regarding select in field

Anonymous — Thu, 09 Jun 2016 17:46:25 GMT

hi sunny i have extracted the date and other fields from session start but whats happening is the time stamp of the

session start contains nano seconds i guess , my response time filed is showing minimum response time as 0 secs i want to ceil it to 1

in short 0.5673 as 1 how can we do this stalwar1‌

Re: Help required regarding select in field

sunny_talwar — Thu, 09 Jun 2016 17:48:11 GMT

May be try this: Ceil(FieldName, (1/(24*60*60)))