Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
ROW | CUSTOMERID | TIMESTAMP | RATING |
---|---|---|---|
1 | C086 | 2014/02/05 18:00:00 | 21 |
2 | C086 | 2014/02/05 19:30:00 | 18 |
3 | C086 | 2014/02/05 21:30:00 | 23 |
4 | C086 | 2014/02/05 23:45:00 | 15 |
5 | C086 | 2014/02/06 00:45:00 | 12 |
6 | C086 | 2014/02/06 04:00:00 | 17 |
7 | C147 | 2014/02/05 22:30:00 | 25 |
8 | C147 | 2014/02/06 06:00:00 | 28 |
9 | C147 | 2014/02/06 09:30:00 | 11 |
10 | C147 | 2014/02/06 10:30:00 | 16 |
11 | C147 | 2014/02/06 12:00:00 | 21 |
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)
ROW | CUSTOMERID | TIMESTAMP | RATING | <= 2 HOURS FLAG |
---|---|---|---|---|
1 | C086 | 2014/02/05 18:00:00 | 21 | T |
2 | C086 | 2014/02/05 19:30:00 | 18 | T |
3 | C086 | 2014/02/05 21:30:00 | 23 | T |
4 | C086 | 2014/02/05 23:45:00 | 15 | F |
5 | C086 | 2014/02/06 00:45:00 | 12 | T |
6 | C086 | 2014/02/06 04:00:00 | 17 | F |
7 | C147 | 2014/02/05 22:30:00 | 25 | F |
8 | C147 | 2014/02/06 06:00:00 | 28 | F |
9 | C147 | 2014/02/06 09:30:00 | 11 | T |
10 | C147 | 2014/02/06 10:30:00 | 16 | T |
11 | C147 | 2014/02/06 12:00:00 | 21 | T |
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'))
ROW | CUSTOMERID | TIMESTAMP | RATING | <= 2 HOURS FLAG | FLAG |
---|---|---|---|---|---|
1 | C086 | 2014/02/05 18:00:00 | 21 | T | First |
2 | C086 | 2014/02/05 19:30:00 | 18 | T | Second |
7 | C147 | 2014/02/06 04:30:00 | 25 | F | First |
8 | C147 | 2014/02/06 06:00:00 | 28 | F | Second |
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
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
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