topic logic help in QlikView

logic help

arusanah — Tue, 19 Jul 2016 14:45:16 GMT

Null

I have a field as above . this is for cust ID =1 , I wan to store after null value i.e "C" in new column . the logic I am applying is not working .

If([CUST_ID] = Previous([CUST_ID])

and (isnull(Previous([GRP])) and RecNo()<>1 ) ,

IF (ISNULL([GRP]),0,[GRP])) as [NEW_GRP]

Any help is greatly appreciated !

Re: logic help

sunny_talwar — Tue, 19 Jul 2016 14:53:22 GMT

What is CUST_ID and GRP here?

Re: logic help

jonathandienst — Tue, 19 Jul 2016 15:09:11 GMT

This is a simplification of your expression:

If([CUST_ID] = Previous([CUST_ID]) And IsNull(Previous([GRP])), If(IsNull(GRP), 0 GRP), 0) as [NEW_GRP]

If([CUST_ID] = Previous([CUST_ID]) And IsNull(Previous([GRP])), If(IsNull(GRP), 0 GRP), GRP) as [NEW_GRP]

But I am not sure what you are trying to achieve. I suggest that you provide a more complete example with source data, explaining clearly and exactly what output you would like.

Re: logic help

arusanah — Tue, 19 Jul 2016 16:56:49 GMT

with The expression you sent , its just eliminating null values in the Grp column and copying other values into new grp . where as my requirement is to store only value that is diaplayed after null in new group.

id	grp
1	A	New group should contain only
1	null	C
1	null	D
1	null
1	C
2	B
2	B
2	Null
2	D

where as my requirement is to store only value that is displayed after null in new group.

Re: logic help

arusanah — Tue, 19 Jul 2016 17:10:30 GMT

Hi Sunny ,

CUST_ID is recipient id & Grp is Plan they are in. person can move from one plan to another. so in new field we want to store information of the grp where he moved to .

Re: logic help

Digvijay_Singh — Tue, 19 Jul 2016 18:41:21 GMT

if you are always storing the last value, then u can try this script -

Source:

Load * inline [

id, grp

1, A

1, null

1, C

2, B

2, Null

2, D ];

NoConcatenate

Final:

Load id,SubField(grp,',',-1) as grp;

Load

id,concat(grp,',',RecNo()) as grp

resident Source

group by id

;

drop Table Source;

Re: logic help

maxgro — Tue, 19 Jul 2016 19:11:40 GMT

My idea (hope to understand your question) in the attachment