Need your help

[aarnkalle]

Hi

I am trying to put below conditions ONLY IF SendDate is latest but i am not getting correct output. Please suggest.

If ((Net <> 0) AND RangeMax ((SendDate)) ,

  If ((([AmtRcd] + [RecoveryAmt]) = 0) and (len ([DenialCode]) < 2) , 'Pending for Action' ,    

     If ((([AmtRcd] + [RecoveryAmt]) = 0) and (len([DenialCode]) > 2), 'Rejected' ,

     If ((([AmtRcd] + [RecoveryAmt]) > 0) and (len([DenialCode]) > 2), 'Rejected' ,

     If ((([AmtRcd] + [RecoveryAmt]) > 0) and (([Net]-([AmtRcd]) + ([RecoveryAmt]))> 0) , 'Rejected',

     If ((([Net]-([AmtRcd]) + ([RecoveryAmt]))) <0, 'Probable Take Back' )))))) , 'Pending for Action' AS [Payment Status] ;

Thanks and Regards,

Abhijit.

[petter · Accepted solution]

Using RangeMax like this will always return the actual date of SendDate and not a maximum.

You might have to determine Max(SendDate) separately before this load and then assign this to a variable so you can compare in your subsequent load statement.

MAXSENDDATE:

LOAD Max(SendDate) AS MaxSendDate RESIDENT someTable;

vMaxSendDate = Peek('MaxSendDate',0,'MAXSENDDATE');

DROP TABLE MAXSENDDATE;

LOAD

    ....

    .....

  If ((Net <> 0) AND SendDate = $(vMaxSendDate)  ,

    If ((([AmtRcd] + [RecoveryAmt]) = 0) and (len ([DenialCode]) < 2) , 'Pending for Action' ,   

      If ((([AmtRcd] + [RecoveryAmt]) = 0) and (len([DenialCode]) > 2), 'Rejected' ,

      If ((([AmtRcd] + [RecoveryAmt]) > 0) and (len([DenialCode]) > 2), 'Rejected' ,

        If ((([AmtRcd] + [RecoveryAmt]) > 0) and (([Net]-([AmtRcd]) + ([RecoveryAmt]))> 0) , 'Rejected',

          If ((([Net]-([AmtRcd]) + ([RecoveryAmt]))) <0, 'Probable Take Back' )))))) , 'Pending for Action' AS [Payment Status] ;

[aarnkalle]

Thanks for your help.

[petter]

You're welcome - glad to help