logic help

[arusanah]

A

Null

Null

Null

C

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 !

[sunny_talwar]

What is CUST_ID and GRP here?

[jonathandienst]

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]

or

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.

[arusanah]

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.

[arusanah]

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 .

[Digvijay_Singh]

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

Source:

Load * inline [

id, grp

1, A

1, null

1, null

1, null

1, C

2, B

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;

[maxgro]

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