Group by in Resident Load issue

[sarahshong]

Hello,

I am having an issue aggregating my price output to a certain level. I have my first data table left joined to another table that maps out missing weekends and holiday dates in Excel. From there I am trying to map each day in the month to a particular cycle (days 1-10 are cycle 1, days 11-20 are cycle 2, days 21-31 are cycle 3).

I want to be able to calculate an average from per cycle for a given Index (IDX_NM), Gridpoint Label, and Month (EODMonth).

I've tried a few variations of the script below and not sure what else to try. The goal is to have a average price (CYCLE_AVG) per day. So the CYCLE_AVG for Jan 1-Jan10 for a IDX_NM/GRIDPT_LABEL_TXT would be the same value. 

Any ideas on how I can achieve this?  The reason I would like to create a field for this in the script versus a formula in a pivot chart is because I was having difficulty multiplying daily values with aggregated averages without showing CYCLE as a dimension.

Prices:
LOAD
DATE as DATE_OLD,
IDX_NM,
OUTPUT_PRC,
GRIDPT_LABEL_TXT,
FROM Price (qvd)
WHERE DATE>'12/30/18' and DATE<'02/28/2019' ;

Left Join
LOAD 

DATE as DATE_OLD,
DATE_NEW AS DATE,
Month(DATE) as EODMonth,
Day(DATE) as EODDay,
Year(DATE) as EODYear,
DAYOFWEEK

FROM
[Dates.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE DATE>'12/30/18' and DATE<'02/28/2019';

LOAD

Day as EODDay,
Cycle as CYCLE
FROM
[Cycle.xlsx]
(ooxml, embedded labels, table is Sheet2);

No Concatenate
LOAD
IDX_NM,
GRIDPT_LABEL_TXT,
EODMonth,
CYCLE,
Avg(OUTPUT_PRC) as CYCLE_AVG

Resident Prices Group by IDX_NM,GRIDPT_LABEL_TXT,EODMonth,CYCLE;

[Kushal_Chawda]

@sarahshong  try below

Prices:
LOAD
DATE as DATE_OLD,
IDX_NM,
OUTPUT_PRC,
GRIDPT_LABEL_TXT,
FROM Price (qvd)
WHERE DATE>'12/30/18' and DATE<'02/28/2019' ;

Left Join
LOAD 

DATE as DATE_OLD,
DATE_NEW AS DATE,
Month(DATE) as EODMonth,
Day(DATE) as EODDay,
Year(DATE) as EODYear,
DAYOFWEEK

FROM
[Dates.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE DATE>'12/30/18' and DATE<'02/28/2019';

left join

LOAD

Day as EODDay,
Cycle as CYCLE
FROM
[Cycle.xlsx]
(ooxml, embedded labels, table is Sheet2);

No Concatenate
LOAD
IDX_NM,
GRIDPT_LABEL_TXT,
EODMonth,
CYCLE,
Avg(OUTPUT_PRC) as CYCLE_AVG

Resident Prices Group by IDX_NM,GRIDPT_LABEL_TXT,EODMonth,CYCLE;

drop table Prices;

[sarahshong]

Hi Kush thanks for replying !

Unfortunately I cannot drop the prices table at the end because then I lose the OUTPUT_PRC and other Date fields that I still need. What I'm looking for is for CYCLE AVG to be equal to the average of the prices for that cycle and associate that to each individual day (DATE) within that cycle ,

[sarahshong · Accepted solution]

I believe I found the solution. I had to right join the last table instead of no concatenate. Thank you for your help!