Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

aarnkalle
New Contributor II

Need your help

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Need your help

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] ;




3 Replies
MVP
MVP

Re: Need your help

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
New Contributor II

Re: Need your help

Thanks for your help.

MVP
MVP

Re: Need your help

You're welcome - glad to help

Community Browser