Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Comparisons

I have two tables linked by a single ID. Messages in the first table are incoming messages. Messages from the second table are outgoing. We have more outgoing messages than incoming. I need to condense my outgoing messages based on what messages are within five days of an incoming message and lose the rest. Then I need to figure out the response time between the closest matched dates. I am fairly new to this program and any advice is appreciated.

Medcompare.JPG.jpg

Thanks,

Nathan

Tags (1)
1 Solution

Accepted Solutions
jpe
Not applicable

Re: Re: Re: Comparisons

Perfect. Thanks

I used this expression to check the interval, locate the shortest interval in each grouping of ReceivedDate and render that value only.  If you have multiple PIDs you may have to add  <PID,ReceivedDate> below where there is only <ReceivedDate> to make the grouping unique to the combination of PID and receiveddate.

Capture.PNG.png

if(

  Interval( fabs(CommSentDate-ReceivedDate),'hh:mm') = min ( total  <ReceivedDate>  Interval( fabs(CommSentDate-ReceivedDate),'hh:mm')) ,

  Interval( fabs(CommSentDate-ReceivedDate),'hh:mm')

)

11 Replies
jpe
Not applicable

Re: Comparisons

You could use an IF() statement and check the date differential to ensure its less than 5 days apart.  Qlik's associative data model will ensure the IDs are always matched

if(  [DateOutgoing]-[DateIncoming] <=5 ,   sum( [DateOutgoing]-[DateIncoming]) )

This expression should figure out the minimum DateOutgoing  for each ID.

min ( Total  <ID >  DateOutgoing)    

Not applicable

Re: Comparisons

Would you use both statements within the outgoing message expression?

jpe
Not applicable

Re: Comparisons

So in the table on the right try:

1. Adding the expression '[DateOutgoing]-[DateIncoming] '  and see if it works and returns the right day #'s

2. If it works, change it to :   ' if(  [DateOutgoing]-[DateIncoming] <=5 ,   [DateOutgoing]-[DateIncoming] )' and see if that works. 

3. if that works, try disabling any other expression in the chart, it should filter it down has 0 values in expressions (so long as all expression in the chart have zeroes' )  are by default auto suppressed. 



ps:  I'm not sure what context you need the minimum date... that wasn't clear in the first post

pps:  this is a bit of guesswork. its better to have an example to use .

Not applicable

Re: Comparisons

I created a table with both received date and outgoing dates.

Then applied if([CommSentDate]-[ReceivedDate] <=5 ,sum([CommSentDate]-[ReceivedDate]))

Time.JPG.jpg

It appears to duplicate received dates and sent dates. It also does not match up in the table.

The second expression-min (Total<Id> CommSentDate) gave me the following:

Time 2.JPG.jpg

The min date would be 1/1/2014 for a starting point. The thing is, I need the closet date from the start of each received message to match up with the closet sent date without confusion of past or future dates. Then I need to calculate what the date/time difference is between each compared date/time to figure out an average response time frame. I work in the medical field and the data I have cannot be shared, unfortunately. 

I figured I'd start at a 5 day window. My hope is that messages are answered within 48 hours.

jpe
Not applicable

Re: Comparisons

Could you post a few rows of representative data that isn't real as well as a picture or of the desired outcome ?  You can type both out in an excel spreadsheet.

Not applicable

Re: Re: Comparisons

This would be the desired outcome from the data above.

IDReceived DateSent DateTime Response Within
27001/1/2014 11:59:24 AM1/2/2014 2:55:24 PM27 hours
27002/20/2014 10:16:30 AM2/21/2014 8:46:32 AM22 hours
27005/26/2014 9:09:01 PM5/27/2014 8:31:33 AM23 hrs

I'm not particular about how the time response is presented as long as I can read easily.

ex:

0 days, 23 hours, 22 minutes and 32 seconds

or

23 hours

jpe
Not applicable

Re: Comparisons

Can i ask you to export the chart data to xls ?  It will save me typing it all out from the screenshot and also help you ultimately have an example that you can use with minimal translation back to your environment.

Not applicable

Re: Re: Comparisons

Let me know if this does not work for you.

jpe
Not applicable

Re: Re: Re: Comparisons

Perfect. Thanks

I used this expression to check the interval, locate the shortest interval in each grouping of ReceivedDate and render that value only.  If you have multiple PIDs you may have to add  <PID,ReceivedDate> below where there is only <ReceivedDate> to make the grouping unique to the combination of PID and receiveddate.

Capture.PNG.png

if(

  Interval( fabs(CommSentDate-ReceivedDate),'hh:mm') = min ( total  <ReceivedDate>  Interval( fabs(CommSentDate-ReceivedDate),'hh:mm')) ,

  Interval( fabs(CommSentDate-ReceivedDate),'hh:mm')

)