topic Re: Script where Today is between Start and End Date in QlikView

Script where Today is between Start and End Date

Anonymous — Wed, 20 Jul 2016 11:35:06 GMT

Hi Guys

I need a script to return the only the record for:

where Today() is between Start Date Key and End Date Key

So the Table will be reduced based on one Coy No Record where Today(20170720) is between (Start Date Key and End Date Key)

Script dummy:

CompanyNumber:

LOAD [Coy No],

[Start Date Key],

[End Date Key]

FROM

Between.xls

(biff, embedded labels, table is Sheet1$);

Coy No	Start Date Key	End Date Key
111111	20160801	99991231
111111	20160701	20160731
111111	20160530	20160630
111111	20160510	20160529
111111	20160501	20160509
111111	20160401	20160430
111111	20160320	20160331
111111	20160301	20160319
111111	20160201	20160229
111111	20151212	20160131
111111	20151204	20151211
111111	20151124	20151203
111111	20151123	20151123
111111	20151117	20151122
111111	20151009	20151116
111111	20150907	20151008
111111	20150725	20150906
111111	20150629	20150724
111111	20150507	20150628

Re: Script where Today is between Start and End Date

sunny_talwar — Wed, 20 Jul 2016 13:48:14 GMT

May be like this:

CompanyNumber:

LOAD [Coy No],

[Start Date Key],

[End Date Key]

FROM

Between.xls

(biff, embedded labels, table is Sheet1$)

Where [Start Date Key] <= Today() and [End Date Key] >= Today();

Re: Script where Today is between Start and End Date

Anonymous — Thu, 21 Jul 2016 09:07:00 GMT

Thanks Sunny

Appreciate it

Re: Script where Today is between Start and End Date

MindaugasBacius — Thu, 21 Jul 2016 09:17:38 GMT

Use this:

LOAD [Coy No] 
     ,date(date#([Start Date Key], 'YYYYMMDD'), 'YYYY-MM-DD') as [Start Date Key] 
     ,date(date#([End Date Key], 'YYYYMMDD'), 'YYYY-MM-DD') as [End Date Key]
FROM
[https://community.qlik.com/thread/225815]
(html, codepage is 1257, embedded labels, table is @1)
where Today() >= date(date#([Start Date Key], 'YYYYMMDD'), 'YYYY-MM-DD') and Today() <= date(date#([End Date Key], 'YYYYMMDD'), 'YYYY-MM-DD')
;

Result:

See the attachment.

Re: Script where Today is between Start and End Date

oknotsen — Thu, 21 Jul 2016 10:20:40 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

Re: Script where Today is between Start and End Date

Anonymous — Thu, 21 Jul 2016 15:27:51 GMT

There is no button like that on my profile. Not sure if this is a glitch.

Can only reply or like and actions is 'none'

Re: Script where Today is between Start and End Date

Anonymous — Thu, 21 Jul 2016 20:51:18 GMT

Hi Chris,

In order to have the options to mark posts as Correct or Helpful, you will need to view the actual thread in the community. Those options are not available when replying by email or when viewing in your Inbox & Activity area of your profile.

Here's a link to the thread itself:Script where Today is between Start and End Date

Let me know if you're still having issues after trying this route.

Best regards,

Qlik Community Team

Re: Script where Today is between Start and End Date

MindaugasBacius — Fri, 22 Jul 2016 04:59:16 GMT

It's practically the same as marking the answer Helpful as you did for stalwar1 replay.

Re: Script where Today is between Start and End Date

Anonymous — Fri, 22 Jul 2016 06:43:04 GMT

Thanks, Correct button was missing on the Inbox link and could only mark as helpful/reply/like.

I did ask Qlik Qoncierge <qoncierge@qlik.com> to resolve and I now see the button again.

Thanks guys and Sunny for the Assistance