topic Re: Date Function(Current and Future Dates) in QlikView

Date Function(Current and Future Dates)

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Date Function(Current and Future Dates)

supriyabiware — Tue, 12 Dec 2017 11:49:02 GMT

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

Re: Date Function(Current and Future Dates)

Anil_Babu_Samineni — Tue, 12 Dec 2017 12:14:33 GMT

May be this? Qlik is case sensitive

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

Re: Date Function(Current and Future Dates)

vishsaggi — Tue, 12 Dec 2017 13:29:51 GMT

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')))

Re: Date Function(Current and Future Dates)

vishsaggi — Tue, 12 Dec 2017 13:37:11 GMT

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?

Re: Date Function(Current and Future Dates)

bhavvibudagam — Tue, 12 Dec 2017 13:44:59 GMT

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,

Re: Date Function(Current and Future Dates)

cweiping — Tue, 12 Dec 2017 14:02:06 GMT

Is it like this?

Re: Date Function(Current and Future Dates)

bhavvibudagam — Tue, 12 Dec 2017 14:09:15 GMT

Hi,

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

I don't have qlikivew license.

Thanks

Re: Date Function(Current and Future Dates)

cweiping — Tue, 12 Dec 2017 14:12:50 GMT

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

Re: Date Function(Current and Future Dates)

bhavvibudagam — Tue, 12 Dec 2017 14:26:49 GMT

Hi,

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

Thanks in advance

Re: Date Function(Current and Future Dates)

cweiping — Tue, 12 Dec 2017 14:34:17 GMT

If date-today is greater than 0 then return 1, equal to 0 is returned, and less than -1.

So 1 represents the future date, 0 represents the current date, and -1 is expired.

If sign (date-today) plus 1 sign again then 1 represents the future and now, and 0 becomes the past date.