topic Re: Creating a week field based on system date? in QlikView

Creating a week field based on system date?

Fri, 03 Jun 2016 13:35:14 GMT

Hi all there,

How can I create a weekend dates field using system date and weekend date should be every Sunday??

Example:

Weekend

05/01/2016

05/08/2016

05/15/2016

05/22/2016

05/29/2016

likewise for every month??

Re: Creating a week field based on system date?

sunny_talwar — Fri, 03 Jun 2016 13:39:51 GMT

What's the significane of system date here? WeekEnd would be a weekend right, or am I missing something important here?

Re: Creating a week field based on system date?

MindaugasBacius — Fri, 03 Jun 2016 13:40:22 GMT

I strongly recommend you in the Master calendar just add one additional flag like:

If(weekday(date) = 6, 1, 0) as Weekend_flag;

Re: Creating a week field based on system date?

Fri, 03 Jun 2016 13:52:03 GMT

I have one date field in data, but i should not use that date field to get the weekend dates, so i thought that it can be done using system date, if any other possibility, let me know

Thanks

Prashanth

Re: Creating a week field based on system date?

sunny_talwar — Fri, 03 Jun 2016 13:55:47 GMT

Once again. I am not sure what system date needs to do here? System date should be 06/03/2015 (today), what does that need to do with the weekend? Do you just want the weekend based on Today's date? try this

WeekEnd(Today(), 0, 0)

Re: Creating a week field based on system date?

Fri, 03 Jun 2016 13:56:09 GMT

I have only one date field which i should not use to create weekend dates field. so i thought like system will do the job. Is there any possibility to get the same without any date column?

thanks

Prashanth

Re: Creating a week field based on system date?

Fri, 03 Jun 2016 14:01:36 GMT

Okay let me clarify you one thing. I have date field like

Postdate

05/04/2016

05/09/2016

05/15/2016

05/17/2016

I need to create weekend dates for respective records

Expected:

Postdate CreatedWeendend

05/04/2016 05/08/2016

05/09/2016 05/15/2016

05/12/2016 05/0152016

05/17/2016 05/22/2016

thanks

Prashanth

Re: Creating a week field based on system date?

sunny_talwar — Fri, 03 Jun 2016 14:04:30 GMT

Try like this:

SET FirstWeekDay=0;

LOAD Postdate,

WeekEnd(Postdate, 0, 0) as CreatedWeendend

FROM Source;

UPDATE:
Added an environmental variable

Re: Creating a week field based on system date?

Fri, 03 Jun 2016 14:48:32 GMT

sorry to confuse you, but I should not use the Post date field. Any other way that can do the job??

Re: Creating a week field based on system date?

sunny_talwar — Fri, 03 Jun 2016 14:56:46 GMT

But if you are using Postdate as reference point, how can you not use it? Outside QlikView how would you do this?

Re: Creating a week field based on system date?

Fri, 03 Jun 2016 16:00:18 GMT

I have a scenario like,

Week1:

Post date

03/31/2016

Week2:

Post date

03/31/2016

Week3:

Post date

03/31/2016

Week1:

Post date

03/31/2016

......

for a ticket which is pending in every week (I am getting data weekly), and I have number of tickets of this type.

I need to calculate how many tickets are pending greater than 7 days,

For the scenario above mentioned, It should be counted in every week(expect first week).

For this purpose I need create a weekend for every week as I mention above.

(If I use the Post date to create the weekend, in every i am getting the same weekend)

Re: Creating a week field based on system date?

sunny_talwar — Fri, 03 Jun 2016 16:05:36 GMT

Oh so you might need another date which is not linked to Postdate and may be do an IntervalMatch between Postdate as start date and Today as enddate and the mastercalendar to make this to work

Re: Creating a week field based on system date?

Fri, 03 Jun 2016 16:12:58 GMT

Exactly, and could you please elaborate to implement the same at script level

Note: If I have to use the mastercalndar I must use on date field, I should not use the one I have.

Re: Creating a week field based on system date?

sunny_talwar — Fri, 03 Jun 2016 16:32:40 GMT

Something like this?

Script:

Table:

LOAD Tickets,

Postdate,

Date(WeekEnd(Postdate, 0, 0) + (7 * IterNo()) - 7) as CreatedWeendend

While WeekEnd(Postdate, 0, 0) + (7 * IterNo()) - 7 <= WeekEnd(Today(), 0, 0);

LOAD * INLINE [

Tickets, Postdate

A, 05/04/2016

B, 05/09/2016

C, 05/15/2016

D, 05/17/2016

];