Discussion Board for collaboration related to Creating Analytics for QlikView.
I just reach an impasse.
I need split data at script level:
Inline tablewith Sundays Dates)
Start End value
02/05/16 31/05/16 29
I need to find a way to return table like this:
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
What is the logic for dividing the Value? Is it one for each day?
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
I have found a solution but it´s a bit large.
I hope helps you.
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??
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.
Here's the script:
LOAD * INLINE [
LOAD * Resident DayAdder;
DROP Table DayAdder;
Date(WeekEnd - DayAdder) as Date
Drop Table Weeks;
//Up to this point we've just fleshed out a simple calendar of WeekEnd dates and the dates that pertain
RowNo() as PeriodID,
Start, End, Value
IntervalMatch(Date) LOAD Start, End Resident Periods;
1 as Counter,
Drop Table Periods;
Drop Table [Periods-1];
If(CumCounter <= Value,1,0) as Cnt;
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.