Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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] ;
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] ;
Thanks for your help.
You're welcome - glad to help