Turn monthly amounts into daily averaged amounts in load script

dhamilton — Thu, 03 Aug 2023 20:20:32 GMT

I have a dashboard with multiple data sources, some providing daily dated totals and some providing monthly totals. On the monthly data source, I am trying to convert the monthly totals into dated daily averaged totals for that month. How could I go about doing this?

So in the below script, for each recordid, i have amount dated 1st of the month. I need to change this so that instead of having 1 amount for each record, I have 28-31 records for each day of the month, depending how long the month is.
- i.e. : (recordid=1, amount=93, DFRDate=08/1/2022) Becomes: (recordid=1a, amount=3, DFRDate=8/1/22), (recordid=1b, amount=3, DFRDate=8/2/22),(recordid=1c, amount=3, DFRDate=8/3/22) etc.

LOAD
RecordId as recordid,
MakeDate(Left(YYYYMM,4),Right(YYYYMM,2)) as DFRDate,
recordtype,
amount

FROM [lib://I2C/2.UserData/USICDataPre8.22.xlsx]
(ooxml, embedded labels, table is USICDataPreAug22);

Thank you!

Re: Turn monthly amounts into daily averaged amounts in load script

vinieme12 — Fri, 04 Aug 2023 03:31:52 GMT

As below

temp:
Load
*
,iterno()-1 as new_recordid
,amount/day(MonthEnd(DFRDate)) as new_amount
,Date(DFRDate + iterno()-1) as new_DFRDate
While DFRDate + iterno()-1 <= MonthEnd(DFRDate)
;
load recordid,amount,date#(DFRDate,'MM/DD/YYYY') as DFRDate inline [
recordid,amount,DFRDate
1,93,08/1/2022
];

exit Script;

Main:

Load
*
,iterno()-1 as new_recordid
,amount/day(MonthEnd(DFRDate)) as new_amount
,Date(DFRDate + iterno()-1) as new_DFRDate
While DFRDate + iterno()-1 <= MonthEnd(DFRDate)
;

LOAD
RecordId as recordid,
MakeDate(Left(YYYYMM,4),Right(YYYYMM,2)) as DFRDate,
recordtype,
amount
FROM [lib://I2C/2.UserData/USICDataPre8.22.xlsx]
(ooxml, embedded labels, table is USICDataPreAug22);

topic Re: Turn monthly amounts into daily averaged amounts in load script in App Development

Turn monthly amounts into daily averaged amounts in load script

Re: Turn monthly amounts into daily averaged amounts in load script