remove development

[#1]

Hi All,

I am facing a issue .I have One tables that thave three Columns

STATUS,PNUMBER & PNAME

Table is

Here PolicyNUMBER  is Id, But sometime it is repeated.

I If PolicyNUMBER   is same for two status then column of Dead should delete.

, if for One PolicyNUMBER   only one Dead Status then it shouldn't delete.

If Same PolicyNUMBER   having two two DEAD STATUS  then any of  one should deleted.

I AM ATTACHING XLS.HERE YELLOW COLUMNS SHOULD DELETED.

I am attaching here result also.

[Anonymous]

Does this give you food for thought ?

Temp:

LOAD * INLINE [

PolicyNumber, PolicyName, Products,

1001, Inforce, ABC

1001, DEAD, ABBB

1002, Decline, HHJKJ

1002, DEAD, JHJG

1003, Raised, JHG

1004, Inforce, KIFF

1005, DEAD, JHV

1006, DEAD, NNBVF

1006, Inforce, GHH

1007, DEAD, KJJJ

1007, Decline, LLL

1008, DEAD, UUU

1008, DEAD, FFF

];

WithDeadFlag :

load

  if ( PolicyName = 'DEAD' , 1 , 0 ) as DeadFlag ,

  *

resident Temp

;

WithoutDead :

load

  *

where not ( PolicyNumber = PreviousPolicyNumber and  PolicyName = 'DEAD' )

;

load

  previous ( PolicyNumber ) as PreviousPolicyNumber ,

  *

resident WithDeadFlag

Order by PolicyNumber , DeadFlag

;

drop table Temp ;

drop table WithDeadFlag ;