help with expresssion in load script

[arusanah]

User ID  Month  Plan Grp Code  Plan Grp

1111  jan  1   A
1111  Feb  1   A
1111  Mar  2   B
1111  Apr  3   c
1111  May  -   -
 

I have to create a new field Disenrolled Field, where when a user changes group or has null  it should be 1 ,0. but the catch is it has to set 1 as disenrolled indicator in previous record. i was writting query as below but it sets 1 in current record and not in previous record.

If(([user Id] = Previous([user Id])
   And ([Plan Grp Code]<> previous([Plan Grp Code]) or ( isnull([Plan Grp Code]) and IsNull([Plan Grp])))),
            1,0) as [DisenrolledIndicator].

BTW is there any function as next() in qlikview.

[sunny_talwar]

There is no such thing as Next, but you can reverse sort your table and then use Previous which acts just the same way as you would expect Next to behave

[florentina_doga]

aa:

load *,

If(([UserID] = Previous([UserID])

   And ([PlanGrpCode]<> previous([PlanGrpCode]) or ( isnull([PlanGrpCode]) and IsNull([PlanGrp])))),

            1,0) as [DisenrolledIndicator];

load * inline [UserID,  Month,  PlanGrpCode,  PlanGrp

1111,  1,  1,   A

1111,  2,  1,   A

1111,  3,  2,   B

1111,  4,  3,   c

1111,  5,  ,

];

[florentina_doga]

or maybe like this

aa:

load * inline [UserID,  Month,  PlanGrpCode,  PlanGrp

1111,  1,  1,   A

1111,  2,  1,   A

1111,  3,  2,   B

1111,  4,  3,   c

1111,  5,  ,

];

NoConcatenate

bb:

load *,

If(([UserID] = Previous([UserID])

   And ([PlanGrpCode]<> previous([PlanGrpCode]) or ( isnull([PlanGrpCode]) and IsNull([PlanGrp])))),

            1,0) as [DisenrolledIndicator];

load *

resident aa order by UserID,Month desc;

drop table aa;

[arusanah]

but how can I create / maintain inline for 5 yrs data for a user id

[miguelbraga]

You don't need to create a inline, you've just need to import from a Excel, CSV File your data and you're good to go

Regards,

MB

[florentina_doga]

replace my inline with your source.