Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Help with load script!

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Help with load script!

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.


talk is cheap, supply exceeds demand
3 Replies
Not applicable

Re: Help with load script!

Hello Rahul,

You can find attached the requested script.

Hope this helps.

MVP & Luminary
MVP & Luminary

Re: Help with load script!

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Help with load script!

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

Regards

Rahul

Community Browser