Date Function(Current and Future Dates)

[bhavvibudagam]

Hi Experts,

Can any one please help me on this.

I have Two fields named Status and Date fields.Those two fields contains the data like

Status

CURRENT

EXPIRED

NONE

Date

10 December 2017

15 February 2018

19 December 2018

11 December 2017

12 December 2017

if Status is None then Date field value should be blank

If Status is current then Date should be current or future date

If Status is expired then Date should be past date

Thanks in advance.

[supriyabiware]

try this

create 2 fields-

FutureandCurrDate =if(Date>=today(),Date)

ExpiredDate           =if(Date<today(),Date)

here instead of today() you can put any threshold.

=pick(match(Status,

  'Current',

  'Expired',

  'None'),

    FutureandCurrDate,

    ExpiredDate,

    'Blank'

   )

Thanks

Supriya

[Anil_Babu_Samineni]

May be this? Qlik is case sensitive

Pick(Match(Status, 'CURRENT', 'EXPIRED', 'NONE'), Date(FutureandCurrDate), Date(ExpireDate), '')

[vishsaggi]

May be this? Your condition Date >= Today() evaluates to true or false it will not display a date so try like

=if(Match([Status]='CURRENT') AND [Date] >=today(), [Date],

if(Match([Status]='EXPIRED')     AND [Date] <  today(), [Date],

if(Match([Status]='NONE') ,'Blank')))

[vishsaggi]

My bad.

Match() function should take two parameters. Like

Match(Status, 'Current') So your expr should be like

=if(Match([Status],'CURRENT') AND [Date] >= today(), [Date],

if(Match([Status],'EXPIRED')     AND [Date] <  today(), [Date],

if(Match([Status],'NONE') ,'Blank')))

Can you share your qvf file if possible so that we can quickly check?

[bhavvibudagam]

Hi,

Now expression is Showing OK without error. But not getting the expected output.

Please find the attached app.

Please find the below source file and expected output.

Thanks,

[cweiping · Accepted solution]

Is it like this?

[bhavvibudagam]

Hi,

Yes your correct.Please could you send me the script.

I don't have qlikivew license.

Thanks

[cweiping]

MD_Status:

LOAD * INLINE [

    Status,seq

    CURRENT,1

EXPIRED,0

NONE,3

];

NoConcatenate

LOAD

     Date,

     sign(sign(Date-today())+1) as seq

FROM

[.\Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

[bhavvibudagam]

Hi,

Could you please explain this statement sign(sign(Date-today())+1) as seq

Thanks in advance