Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Timestamp field between two other fields

See attached.  I'm thinking this is a formatting issue but I've lost enough time to figuring it out myself.

I'm trying to write the set analysis for Task_Opened_At is between Visit_FirstViewByDay and Visit_IncidentWindowEnd but nothing has worked for me.  The results should be zero so I'm testing with Task_Opened_At <= Visit_FirstViewByDay and >= Visit_IncidentWindowEnd.  This should count all the records but I'm getting no results.

21 Replies
sunny_talwar

That is very strange because I have not done any manipulation in the script. I just used what you posted above and offered an expression. I will try to reload with the attached file to see what is the difference here

sunny_talwar

Based on the two conditions you have provided:

1) =Task_Opened_At <= Visit_FirstViewByDay

2) =Task_Opened_At >= Visit_IncidentWindowEnd

All your Task Number been the 1st condition, but fail the second condition

Capture.PNG

Are these two conditions correct?

Anonymous
Not applicable
Author

I noted AND in my original post and I guess it should be an OR?  The main thing I need to do is count task numbers when the task was opened outside the 6 hour window (Visit_FirstViewByDay to Visit_IncidentWindowEnd - IncidentWindowEnd is a simple DateAdd(hour,6,Visit_FirstViewByDay_ in my actual  SQL SELECT).

Once I've done that I just need to duplicate the formula and count tasks that were opened in that 6 hour window.

sunny_talwar

Can you re-explain the two condition with an example?

Anonymous
Not applicable
Author

I can and I've also updated the QVW and XLS to show better examples.

I'm trying to write two expressions that I will put into a bar chart with Visit_Date as the dimension.  The bar chart will show a

  1. Inside Visit Window: Distinct count of Task_Number where Task_Opened_At is between Visit_FirstViewByDay and Visit_IncidentWindowEnd
  2. Outside Visit Window: Distinct count of Task_Number where Task_Opened_At is NOT between Visit_FirstViewByDay and Visit_IncidentWindowEnd

In the original post the data did not contain any Tasks opened between the two time stamps.  I've since added test data for this example.

Not applicable
Author

Do you have to use set analysis?  I think you can set up a flag when loading the script.  Please see attached.  I changed data in your xls for testing.

Anonymous
Not applicable
Author

I dont want to say I can't use set analysis but this load script is not my real load script.  Visit information is coming from an SQL server and Task information is coming from a Sybase server.  I'll look at your file shortly.

Not applicable
Author

You should be able to add the flag through residential load.  Also date comparison calculation happens at load script, which, based on my reading, should give better performance than putting it in an UI expression.

Anonymous
Not applicable
Author

This is my first time working with data from two different sources so I'm not sure how I can combine the two to flag this.  Have an example anywhere?  Below is where the data comes from and their table names in my LOAD script.

Visits (SQL):

Visit_FirstViewByDay

Visit_IncidentWindowEnd

Tasks (Sybase):

Task_Number

Task_Opened_At

Not applicable
Author

Please see if this helps.