Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need some help with a load script. I have the following table:
Current Structure | |||
Date | ID | TouchPointStatus | Step |
01-06-2012 20:33 | 500 | E | 1 |
01-06-2012 23:25 | 500 | ET1 | 2 |
02-06-2012 12:58 | 500 | N | ? |
02-06-2012 16:35 | 500 | CustomerCall |
|
29-06-2012 20:34 | 500 | E | 1 |
29-06-2012 23:37 | 500 | ET1 | 2 |
30-06-2012 13:58 | 500 | CustomerCall |
|
30-06-2012 13:58 | 500 | N | ? |
31-07-2012 21:15 | 500 | E | 1 |
01-08-2012 0:02 | 500 | ET1 | 2 |
01-08-2012 13:32 | 500 | N | ? |
10-08-2012 11:33 | 500 | CustomerCall |
|
Each E demarcates the start of the process and N is the end. One Id can be linked to multiple processes. What I need is to create two additional fields like in the table below.
Current Structure | New fields needed | ||||
Date | ID | TouchPointStatus | Step | Final Step | Date for N |
01-06-2012 20:33 | 500 | E | 1 | 1 |
|
01-06-2012 23:25 | 500 | ET1 | 2 | 2 |
|
02-06-2012 12:58 | 500 | N | ? | 3 |
|
02-06-2012 16:35 | 500 | CustomerCall |
| 4 | 02-06-2012 12:58 |
29-06-2012 20:34 | 500 | E | 1 | 1 |
|
29-06-2012 23:37 | 500 | ET1 | 2 | 2 |
|
30-06-2012 13:58 | 500 | CustomerCall |
| 3 |
|
30-06-2012 13:58 | 500 | N | ? | 4 |
|
31-07-2012 21:15 | 500 | E | 1 | 1 |
|
01-08-2012 0:02 | 500 | ET1 | 2 | 2 |
|
01-08-2012 13:32 | 500 | N | ? | 3 |
|
10-08-2012 11:33 | 500 | CustomerCall |
| 4 | 01-08-2012 13:32 |
Final Step is sequence for the particular process and continues till Next E
Date for N captures the date for N when CustomerCall appears after N and before E
Data in excel attached.
Regards
Rahul
Try:
LOAD Date,
ID,
TouchPointStatus,
Step,
if(TouchPointStatus='E',1,peek([Final Step])+1) as [Final Step],
if(TouchPointStatus='CustomerCall' and previous(TouchPointStatus)='N',previous(Date),
if(TouchPointStatus='CustomerCall' and previous(TouchPointStatus='CustomerCall'),peek([Date for N]))) as [Date for N]
FROM
D:\QVdata\comm69038.xlsx
(ooxml, embedded labels, table is Sheet1);
See attached example.
Hello Rahul,
You can find attached the requested script.
Hope this helps.
Try:
LOAD Date,
ID,
TouchPointStatus,
Step,
if(TouchPointStatus='E',1,peek([Final Step])+1) as [Final Step],
if(TouchPointStatus='CustomerCall' and previous(TouchPointStatus)='N',previous(Date),
if(TouchPointStatus='CustomerCall' and previous(TouchPointStatus='CustomerCall'),peek([Date for N]))) as [Date for N]
FROM
D:\QVdata\comm69038.xlsx
(ooxml, embedded labels, table is Sheet1);
See attached example.
Thank you for your help, i got the desierd output using your solution.
Regards
Rahul