How to convert Weekly Data into Daily

[MMCGLAESASAA]

Hello Everyone,

I came up with one scenario where i need to convert weekly date data into Daily date data. 

Below is the input file data.

Week_Date	Employee	Name	Weekly Hrs
8/8/2022	101	Ravi	40
8/8/2022	102	Rajesh	40
8/8/2022	103	Kavita	28
8/8/2022	104	Manoj	38
8/8/2022	105	Vins	40

Need output  as per below.

Date	Employee	Name	Daily Hrs
8/8/2022	101	Ravi	8
8/9/2022	101	Ravi	8
8/10/2022	101	Ravi	8
8/11/2022	101	Ravi	8
8/12/2022	101	Ravi	8
8/8/2022	102	Rajesh	8
8/9/2022	102	Rajesh	8
8/10/2022	102	Rajesh	8
8/11/2022	102	Rajesh	8
8/12/2022	102	Rajesh	8
8/8/2022	103	Kavita	5.6
8/9/2022	103	Kavita	5.6
8/10/2022	103	Kavita	5.6
8/11/2022	103	Kavita	5.6
8/12/2022	103	Kavita	5.6
8/8/2022	104	Manoj	7.6
8/9/2022	104	Manoj	7.6
8/10/2022	104	Manoj	7.6
8/11/2022	104	Manoj	7.6
8/12/2022	104	Manoj	7.6
8/8/2022	105	Vins	8
8/9/2022	105	Vins	8
8/10/2022	105	Vins	8
8/11/2022	105	Vins	8
8/12/2022	105	Vins	8

[JordyWegman · Accepted solution]

Hi,

Use this:

Table:
Load
	WeekYear( Date#( Week_Date , 'DD-MM-YYYY' ) ) & Week( Date#( Week_Date , 'DD-MM-YYYY' ) )  as YearWeek,
    *
;
Load * Inline [
Week_Date	,	Employee	,	Name 	,	Weekly Hrs
8-8-2022	,	101	,	Ravi	,	40
8-8-2022	,	102	,	Rajesh	,	40
8-8-2022	,	103	,	Kavita	,	28
8-8-2022	,	104	,	Manoj	,	38
8-8-2022	,	105	,	Vins	,	40
];

Let varMinDate = Num(Makedate(2022,1,1));
Let varMaxDate = Num(Makedate(Year(today()),12,31));

Left Join (Table)
Load
	WeekYear( Datefield ) & Week( Datefield ) as YearWeek,
	Date( Datefield , 'DD-MM-YYYY' )		  as Date
Where WeekDay( Datefield ) <= 4 
;
LOAD 
	date($(varMinDate)+IterNo()-1) 	AS Datefield
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);


Final:
Load 
	*,
    [Weekly Hrs] / 5 as [Daily Hrs]
Resident Table
;

Drop table Table;

You need to replace of course the Inline table with your own data source.

Jordy
Climber

[MMCGLAESASAA]

Thank you, Jordy, for this code.