Help with load script!

[#1]

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

[#2]

Hello Rahul,

You can find attached the requested script.

Hope this helps.

[Gysbert_Wassenaar · Accepted solution]

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.

[#4]

Thank you for your help, i got the desierd output using your solution.

Regards

Rahul