Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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, ,
];
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;
but how can I create / maintain inline for 5 yrs data for a user id
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
replace my inline with your source.