If I load only the Willow spreadsheet, I get "Field not found error Field '1/1/2019' not found". I have ensured the Excel formats are dates and I have tried date#( [1/1/2019],'M/D/YYYY') as [1/1/2019], and date#(num#(Month),'M/D/YYYY') as Month. For the life of me, I cannot figure out why.
EPIC_HOURS_CROSSTABLE:
UNQUALIFY*;
CrossTable(Month, Hours, 😎
LOAD [GPL Project #],
[Ticket #],
trim(subfield( subfield([Requesting Market],'/'),',')) as EPIC_MARKET,
// if(wildmatch( trim(subfield( subfield([Requesting Market],'/'),',')),'CHI'),'National', trim(subfield( subfield([Requesting Market],'/'),','))) as EPIC_MARKET,
[Project Name],
Comments as HOURS_COMMENTS,
//[Module for Hours],
subfield(SubField([Module for Hours],'/',1),',') as [Module for Hours],
[SA Assigned],
Status as HOURS_STATUS,
date#( [1/1/2019],'M/D/YYYY') as [1/1/2019],
[2/1/2019],
[3/1/2019],
[4/1/2019],
[5/1/2019],
[6/1/2019],
[7/1/2019],
[8/1/2019],
[9/1/2019],
[10/1/2019],
[11/1/2019],
[12/1/2019],
[1/1/2020],
[2/1/2020],
[3/1/2020],
[4/1/2020],
[5/1/2020],
[6/1/2020],
[7/1/2020],
[8/1/2020],
[9/1/2020],
[10/1/2020],
[11/1/2020],
[12/1/2020],
[1/1/2021],
[2/1/2021],
[3/1/2021],
[4/1/2021],
[5/1/2021],
[6/1/2021],
[7/1/2021],
[8/1/2021],
[9/1/2021],
[10/1/2021],
[11/1/2021],
[12/1/2021],
[1/1/2022],
[2/1/2022],
Column1
FROM
[..\External_Data\Epic Capacity Worksheet Willow.xlsx]
(ooxml, embedded labels, header is 8 lines, table is Optimization, filters(
Remove(Col, Pos(Top, 14)),
Remove(Col, Pos(Top, 13)),
Remove(Col, Pos(Top, 12)),
Remove(Col, Pos(Top, 11)),
Remove(Col, Pos(Top, 10)),
Remove(Col, Pos(Top, 9))
));
Final:
LOAD [GPL Project #],
[Ticket #],
if(wildmatch(EPIC_MARKET,'CHI'),'National',EPIC_MARKET) as EPIC_MARKET,
[Project Name],
if(WildMatch([Module for Hours],'MyChart','EC Link','Care Everywhere') ,'Ambulatory',[Module for Hours]) as [Module for Hours],
[SA Assigned],
Hours,
date#(num#(Month),'M/D/YYYY') as Month
RESIDENT
EPIC_HOURS_CROSSTABLE
where addmonths(monthstart(date(today())),-1)>=Month;
//where today()-1>=Month;
Drop TABLE EPIC_HOURS_CROSSTABLE;
