Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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);