Finding Weeks with no value

[Newhaven]

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

[Vegar]

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;

[Newhaven]

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

[Vegar]

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. 

[Kushal_Chawda · Accepted solution]

@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;

[Newhaven]

Perfekt 🙂 Thx