topic Re: Distribute values between dates in QlikView

Distribute values between dates

robert_mika — Wed, 01 Jun 2016 13:28:18 GMT

I just reach an impasse.

I need split data at script level:

Example below

Inline table:(with Sundays Dates)

[ Date

01/05/16

08/05/16

15/05/16

22/05/16

29/55/16

05/06/16

]

Another table:

Start End value

02/05/16 31/05/16 29

I need to find a way to return table like this:

Date Value

08/05/16 6 cause dates 02-07/05

15/05/16 7 08-14/05

22/05/16 7 15-21/05

29/05/16 7 22-28/05

05/06/16 2 because 29-7-7-7-6=2

The second table has more entities.

The one is just an example

Re: Distribute values between dates

sunny_talwar — Wed, 01 Jun 2016 13:36:26 GMT

What is the logic for dividing the Value? Is it one for each day?

Re: Distribute values between dates

robert_mika — Wed, 01 Jun 2016 13:44:40 GMT

Hi Sunny,

01/05 is Sunday

my first day is 02/05 which is Monday

following Sunday (08/05) I need value if 6 (Mon-Sat)

next Sunday 15/05 I need a full week (Sunday 08.05 till Saturday 14.05) -7 days

same for next 2 week

Last week on Sunday the 05.06 I need the value of 2 (for 29.05 and 30.05).

The Dates 02.05 and 31.05 are not being taken into calculations just dates between them.

I hope that makes sense

Re: Distribute values between dates

fvelascog72 — Wed, 01 Jun 2016 15:32:47 GMT

Hi Robert,

I have found a solution but it´s a bit large.

I hope helps you.

Cheers.

Re: Distribute values between dates

robert_mika — Wed, 01 Jun 2016 16:17:01 GMT

Thank you, Federico

I have found a way using a while statement to split the data then summarize.

Distribute values from begin to and end contract date??

Re: Distribute values between dates

effinty2112 — Wed, 01 Jun 2016 20:40:43 GMT

Hi Robert,

I've written a script that appears to get what you want. I've added an extra period

Start, End, Value

02/05/2016, 31/05/2016, 29

01/06/2016, 30/06/2016, 29

and have an inline table of Sundays that extend before and after these periods.

A straight table gives this, WeekEnd 05/06/2016 appears twice because dates in week ending 05/06/2016 straddle the two periods.

PeriodID	WeekEnd	Sum(Cnt)
1	08/05/2016	6
1	15/05/2016	7
1	22/05/2016	7
1	29/05/2016	7
1	05/06/2016	2
2	05/06/2016	4
2	12/06/2016	7
2	19/06/2016	7
2	26/06/2016	7
2	03/07/2016	4

Here's the script:

Weeks:

LOAD * INLINE [

WeekEnd

01/05/2016

08/05/2016

15/05/2016

22/05/2016

29/05/2016

05/06/2016

12/06/2016

19/06/2016

26/06/2016

03/07/2016

10/07/2016

];

DayAdder:

LOAD * INLINE [

DayAdder

];

Join(Weeks)

LOAD * Resident DayAdder;

DROP Table DayAdder;

Dates:

LOAD

WeekEnd,

Date(WeekEnd - DayAdder) as Date

Resident Weeks;

Drop Table Weeks;

//Up to this point we've just fleshed out a simple calendar of WeekEnd dates and the dates that pertain

Periods:

Load

RowNo() as PeriodID,

LOAD * INLINE [

Start, End, Value

02/05/2016, 31/05/2016, 29

01/06/2016, 30/06/2016, 29

];

IntervalMatch(Date) LOAD Start, End Resident Periods;

Left Join([Periods-1])

LOAD

1 as Counter,

Resident Periods;

Drop Table Periods;

Left Join(Dates)

LOAD

Counter,

Date,

PeriodID,

Value

Resident [Periods-1];

Drop Table [Periods-1];

Result:

Load

If(CumCounter <= Value,1,0) as Cnt;

Load

If(PeriodID = Previous(PeriodID),

RangeSum(Counter, Peek('CumCounter')),1) AS CumCounter

Resident Dates Where Counter =1 Order by Date;

DROP Table Dates;

It looks a bit messy and I'm sure it could be simplified.

Kind regards

Andrew