Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have a table with following values.
Week with no value is not writen in the list:
Week | Value |
1 | 10 |
2 | 15 |
3 | 18 |
6 | 3 |
10 | 9 |
13 | 17 |
But I need a List with all weeks of a year including the weeks with no value:
Week | Value |
1 | 10 |
2 | 15 |
3 | 18 |
4 | 0 |
5 | 0 |
6 | 3 |
7 | 0 |
8 | 0 |
9 | 0 |
10 | 9 |
11 | 0 |
12 | 0 |
13 | 17 |
Du you have any idea how to get the missing weeks in the script?
Thx
@Newhaven try this
data:
LOAD *,
Year&Week as YearWeekKey
Inline [
Year Week Value
2021 1 10
2021 2 15
2021 3 18
2021 6 3
2021 10 9
2021 13 17
2020 1 18
2020 2 21
2020 5 19
2020 8 35 ] (delimiter is '\t');
All_Yearweek:
LOAD Year,
Week,
Year&Week as All_YEarWeekKey;
LOAD Year,
min_week+IterNo()-1 as Week
while min_week+IterNo()-1<=max_week;
LOAD Year,
min(Week) as min_week,
max(Week) as max_week
Resident data
Group by Year;
Concatenate(data)
LOAD Year,
Week,
0 as Value
Resident All_Yearweek
where not Exists(YearWeekKey,All_YEarWeekKey);
DROP Table All_Yearweek;
DROP Field YearWeekKey;
You need to generate zero value transactions in some way.
One way to do it, using your sample, is like this:
Transactions:
LOAD * inline [
Week Value
1 10
2 15
3 18
6 3
10 9
13 17] (delimiter is ' ');
MinMaxWeek:
LOAD Min(Week) as MinWeek, Max(Week) as MaxWeek
Resident Transactions;
For _week = peek('MinWeek', -1,'MinMaxWeek') to peek('MaxWeek', -1,'MinMaxWeek') step 1
Concatenate (Transactions)
LOAD
$(_week) as Week,
0 as Value
AutoGenerate 1
Where
not exists(Week, $(_week))
;
next _week
Drop table MinMaxWeek;
And if i need this for a more years:
So for example week = 8 is available in 2020 but missing in 2021?
Year | Week | Value |
2021 | 1 | 10 |
2021 | 2 | 15 |
2021 | 3 | 18 |
2021 | 6 | 3 |
2021 | 10 | 9 |
2021 | 13 | 17 |
2020 | 1 | 18 |
2020 | 2 | 21 |
2020 | 5 | 19 |
2020 | 8 | 35 |
You could do like this
Transactions:
LOAD Year &'-'& Week as YearWeek, * inline [
Year Week Value
2021 1 10
2021 2 15
2021 3 18
2021 6 3
2021 10 9
2021 13 17
2020 1 18
2020 2 21
2020 5 19
2020 8 35] (delimiter is ' ');
MinMaxWeek:
LOAD Min(Week) as MinWeek, Max(Week) as MaxWeek
Resident Transactions;
for each _year in fieldvaluelist('Year')
For _week = peek('MinWeek', -1,'MinMaxWeek') to peek('MaxWeek', -1,'MinMaxWeek') step 1
Concatenate (Transactions)
LOAD
$(_year) &'-'& $(_week) as YearWeek,
$(_week) as Week,
$(_year) as Year,
0 as Value
AutoGenerate 1
Where
not exists(YearWeek, $(_year) &'-'& $(_week))
;
next _week
next _year
Drop table MinMaxWeek;
You need the YearWeek as an unique year and week identifier. You can keep it in the datamodel if you find need for it in the front end or drop it at the end if you don't.
@Newhaven try this
data:
LOAD *,
Year&Week as YearWeekKey
Inline [
Year Week Value
2021 1 10
2021 2 15
2021 3 18
2021 6 3
2021 10 9
2021 13 17
2020 1 18
2020 2 21
2020 5 19
2020 8 35 ] (delimiter is '\t');
All_Yearweek:
LOAD Year,
Week,
Year&Week as All_YEarWeekKey;
LOAD Year,
min_week+IterNo()-1 as Week
while min_week+IterNo()-1<=max_week;
LOAD Year,
min(Week) as min_week,
max(Week) as max_week
Resident data
Group by Year;
Concatenate(data)
LOAD Year,
Week,
0 as Value
Resident All_Yearweek
where not Exists(YearWeekKey,All_YEarWeekKey);
DROP Table All_Yearweek;
DROP Field YearWeekKey;
Perfekt 🙂 Thx