Filter Contract Expiry Date

[sekharQV]

Hi Folks,

I am facing some issues while filtering Contract Expiry date.

i have a date field 'Contract Expiry Date' and i am comparing this field with today to know to know in how many days the contract is going to expire.

So, i have created a multi box with filters like 'Expired','30 Days','60 Days','90 Days','>90 Days' using below expression.

=if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=0,'Expired',

   if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=30,'30 Days',

   if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=60,'60 Days',

   if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=90,'90 Days',

   if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())>90,'>90 Days'

   )))))

When i am selecting 'Expired' or '30 days', it is showing correct result.

But when i select 60 days, it is showing dates which are >30 and <=60.

But the actual result should be, it should show all dates which are expiring with in 60 days. (i.e;from 1 to 60 days).

Same issue is existing for rest of filters(60 days and >90 Days)

I am attaching QVW for your reference.

Could anybody do needful and give me suggestions.

Thanks,

Rajasekha

[rupamjyotidas]

If else won't work in this condition as once conditions are met in first condition it won't be included after that

For Eg: if(a<30,'30',if(a<60,'60')) , '60' won't include below 30 values

You might have to create individual fields for the same in your script

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=0 as 'Expired',

if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=30 as '30 Days',

Etc

[MK_QSL · Accepted solution]

Please don't create multiple thread for same question..

Thanks

[sekharQV]

Hi Rupam Das,

Thanks for reply,

If i create individual fields for each filter from script, then could you please tell me how to show all filters('Expired',30 days,60 days etc) in a single multi box.?

[stabben23]

Hi,

you need to have more and less than, ex if you got an expired 29, it will be less than 30 and less than 60 and less than 90.

[rupamjyotidas]

Manish below has given you the solution.

[#7]

Hi Rajasekhar,

For this you need to use resident load concept with extra Boolean fields.

TEMP:

LOAD

  if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=0,1,0) AS EXPIRED_FIELD,

  if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=30,1,0) AS THIRTY_DAYS_FIELD ,

  if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=60,1,0) AS SIXTY_DAYS_FIELD ,

  if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())<=90,1,0) AS NINTY_DAYS_FIELD ,

  if(fabs(date#(ContractExpiry,'YYYYMMDD')-Today())>90,1,0) AS GREATERTHAN_NINTY_DAYS_FIELD

FROM TABLE;

TEMP:

LOAD

IF(EXPIRED_FIELD=1, 'EXPIRED',

IF( THIRTY_DAYS_FIELD=1 AND SIXTY_DAYS_FIELD=1 AND NINTY_DAYS_FIELD=1 AND GREATERTHAN_NINTY_DAYS_FIELD=1,'>90 Days',

IF( THIRTY_DAYS_FIELD=1 AND SIXTY_DAYS_FIELD=1 AND NINTY_DAYS_FIELD=1

AND GREATERTHAN_NINTY_DAYS_FIELD=0,'90 Days',

IF( THIRTY_DAYS_FIELD=1 AND SIXTY_DAYS_FIELD=1 AND NINTY_DAYS_FIELD=0

AND GREATERTHAN_NINTY_DAYS_FIELD=0,'60 Days',

IF( THIRTY_DAYS_FIELD=1 AND SIXTY_DAYS_FIELD=0 AND NINTY_DAYS_FIELD=0

AND GREATERTHAN_NINTY_DAYS_FIELD=0,'30 Days')  AS EXPIRY_FIELD

RESIDENT TEMP;

DROP TABLE TEMP;

-ASHOK

[sekharQV]

Hi,

Thank you very much for quick reply. It's working.

And i have another scenario.

When i am adding Contract expiry field in a table where there are already some fields, then it is creating concatenation and giving extra rows.

Is there any way to get rid of this?

I attached the QVW file for your reference.

Regards,

Rajasekhar

[MK_QSL]

Close this thread by selecting correct answer if you found your solution and create a new thread.

Mixing two questions in one thread is not a good idea.