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;
