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: 
ciaran_mcgowan
Partner - Creator III
Partner - Creator III

Compare Date/Time with the next field & add flags to script

Hi all,

Apologies for the lengthy post, I want to give as much information as I can because I'm stumped. I have been asked to complete a document that may be out of my current skill set. I am currently reading Customer data from multiple HTML files into display in a QVD but now I need to add the following:

Each customer is supposed to take a Rating score every two hours or earlier, like below.

ROWCUSTOMERIDTIMESTAMPRATING
1C0862014/02/05 18:00:0021
2C0862014/02/05 19:30:0018
3C0862014/02/05 21:30:0023
4C0862014/02/05 23:45:0015
5C0862014/02/06 00:45:0012
6C0862014/02/06 04:00:0017
7C1472014/02/05 22:30:0025
8C1472014/02/06 06:00:0028
9C1472014/02/06 09:30:0011
10C1472014/02/06 10:30:0016
11C1472014/02/06 12:00:0021

Data is not read in date order, but by Customer's date. They want to only see Rating values over 20 and the record that follows it (which I have already done) but they also only want to see records that were taken within the 2 hour limit. For displaying the Rating>=20 I used the Previous() function but for this I think I need to use both the Peek() and Previous() functions to create a flag but nothing I do works (all attempts to use Peek() have resulted in Null values being shown in my tables). I have tried different variations of the below formula but this is it's simplest form:

    If((Peek(TIMESTAMP)-TIMESTAMP)<=Timestamp(HH:MM, '02:00'), T, F)

ROWCUSTOMERIDTIMESTAMPRATING<= 2 HOURS FLAG
1C0862014/02/05 18:00:0021T
2C0862014/02/05 19:30:0018T
3C0862014/02/05 21:30:0023T
4C0862014/02/05 23:45:0015F
5C0862014/02/06 00:45:0012T
6C0862014/02/06 04:00:0017F
7C1472014/02/05 22:30:0025F
8C1472014/02/06 06:00:0028F
9C1472014/02/06 09:30:0011T
10C1472014/02/06 10:30:0016T
11C1472014/02/06 12:00:0021T

The above information will eventually translate to this: Score over 20 AND the row that follows it IF the timestamp is less that two hours. It also needs to flag which is the First, Second, Third, etc valid records (ie. <=2 HOURS FLAG = 'T'). I basically need to view the above table like the one below:

    

      If([<=2 HOURS FLAG] = 'T' AND Peek([<=2 HOURS FLAG] = 'T' ) = 'T', 'First',

          If([<=2 HOURS FLAG] = 'T' AND Previous([<=2 HOURS FLAG] = 'T' ) = 'T', 'Second'......))

Then figure out Compliance:

      If([<=2 HOURS FLAG] = 'T' AND FLAG= 'First' AND Peek(FLAG) = 'Second', 'Compliant',

           If([<=2 HOURS FLAG] = 'T' AND FLAG= 'Second' AND Peek(FLAG) = 'Third', 'Compliant', 'Not Compliant'))

ROWCUSTOMERIDTIMESTAMPRATING<= 2 HOURS FLAGFLAG
1C0862014/02/05 18:00:0021TFirst
2C0862014/02/05 19:30:0018TSecond
7C1472014/02/06 04:30:0025FFirst
8C1472014/02/06 06:00:0028FSecond

Then I can calculate the total number of correct follow ups:

     SUM(If(FLAG='Second' OR FLAG='Third',1,0)

Any help or guidance would be greatly appreciated on this.

Thanks,

Ciarán

2 Replies
Not applicable

Hi Ciaran,

Your resultant table is little confusing. Per your logic only Rate>20 and Flag=T it should show up right?

Can you also explain how you calculated <=2 hour flag for row 7 and 9.

Thanks

AJ

Not applicable

I have attached a sample based on my understanding of your requirement. Please check it out and see if it helps.

Also instead of first,second, third,.....  I did 1,2,3,......

To get the names maybe you can do through applymap() provided you have that in a table.

Thanks

AJ