topic Re: help with expresssion in load script in QlikView

help with expresssion in load script

arusanah — Wed, 07 Sep 2016 13:24:31 GMT

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.

Re: help with expresssion in load script

sunny_talwar — Wed, 07 Sep 2016 13:31:09 GMT

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

Re: help with expresssion in load script

florentina_doga — Wed, 07 Sep 2016 13:35:00 GMT

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, ,

];

Re: help with expresssion in load script

florentina_doga — Wed, 07 Sep 2016 13:42:26 GMT

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;

Re: help with expresssion in load script

arusanah — Wed, 07 Sep 2016 13:42:47 GMT

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

Re: help with expresssion in load script

miguelbraga — Wed, 07 Sep 2016 13:52:59 GMT

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,

Re: help with expresssion in load script

florentina_doga — Wed, 07 Sep 2016 13:59:44 GMT

replace my inline with your source.