Solved: Re: Date# Value format error - Qlik Community

cbaqir · ‎2020-07-10

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;

sunny_talwar · ‎2020-07-10

Try this

EPIC_HOURS_CROSSTABLE:
UNQUALIFY*;
CrossTable(Month, Hours, 8)
LOAD [GPL Project #],
	 [Ticket #],
	 trim(subfield( subfield([Requesting Market],'/'),',')) as EPIC_MARKET,
	 [Project Name],
	 Comments as HOURS_COMMENTS,
	 subfield(SubField([Module for Hours],'/',1),',') as [Module for Hours],
	 [SA Assigned],
	 Status as HOURS_STATUS,
	 [43466], 
     [43497], 
     [43525], 
     [43556], 
     [43586], 
     [43617], 
     [43647], 
     [43678], 
     [43709], 
     [43739], 
     [43770], 
     [43800], 
     [43831], 
     [43862], 
     [43891], 
     [43922], 
     [43952], 
     [43983], 
     [44013], 
     [44044], 
     [44075], 
     [44105], 
     [44136], 
     [44166], 
     [44197], 
     [44228], 
     [44256], 
     [44287], 
     [44317], 
     [44348], 
     [44378], 
     [44409], 
     [44440], 
     [44470], 
     [44501], 
     [44531], 
     [44562], 
     [44593]
FROM [Epic Capacity Worksheet Willow.xlsx]
(ooxml, embedded labels, header is 8 lines, table is Optimization);


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)) as Month
RESIDENT EPIC_HOURS_CROSSTABLE
where addmonths(monthstart(date(today())),-1)>=Month;

Drop TABLE EPIC_HOURS_CROSSTABLE;

View solution in original post

sunny_talwar · ‎2020-07-10

Are you doing a CrossTable Load by any chance?

Kushal_Chawda · ‎2020-07-10

Are you doing cross table load? Would you be able to share sample file?

cbaqir · ‎2020-07-10

Hey Sunny! Yes, Cross tables plural is what I am trying to do but using only one now to troubleshoot. files and code added.

cbaqir · ‎2020-07-10

Added to original post.

sunny_talwar · ‎2020-07-10

Try this 😎

EPIC_HOURS_CROSSTABLE:
UNQUALIFY*;
CrossTable(Month, Hours, 8)
LOAD [GPL Project #],
[Ticket #],
trim(subfield( subfield([Requesting Market],'/'),',')) as EPIC_MARKET,
[Project Name],
Comments as HOURS_COMMENTS,
subfield(SubField([Module for Hours],'/',1),',') as [Module for Hours],
[SA Assigned],
Status as HOURS_STATUS,
[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(Date#(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;

or

Date(Num#(Month)) as Month

But do this only after the CrossTable Load... don't make any changes to CrossTable with regards to date field formatting.

cbaqir · ‎2020-07-10

I'm still getting error. See attached QVW.

sunny_talwar · ‎2020-07-10

Try this

EPIC_HOURS_CROSSTABLE:
UNQUALIFY*;
CrossTable(Month, Hours, 8)
LOAD [GPL Project #],
	 [Ticket #],
	 trim(subfield( subfield([Requesting Market],'/'),',')) as EPIC_MARKET,
	 [Project Name],
	 Comments as HOURS_COMMENTS,
	 subfield(SubField([Module for Hours],'/',1),',') as [Module for Hours],
	 [SA Assigned],
	 Status as HOURS_STATUS,
	 [43466], 
     [43497], 
     [43525], 
     [43556], 
     [43586], 
     [43617], 
     [43647], 
     [43678], 
     [43709], 
     [43739], 
     [43770], 
     [43800], 
     [43831], 
     [43862], 
     [43891], 
     [43922], 
     [43952], 
     [43983], 
     [44013], 
     [44044], 
     [44075], 
     [44105], 
     [44136], 
     [44166], 
     [44197], 
     [44228], 
     [44256], 
     [44287], 
     [44317], 
     [44348], 
     [44378], 
     [44409], 
     [44440], 
     [44470], 
     [44501], 
     [44531], 
     [44562], 
     [44593]
FROM [Epic Capacity Worksheet Willow.xlsx]
(ooxml, embedded labels, header is 8 lines, table is Optimization);


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)) as Month
RESIDENT EPIC_HOURS_CROSSTABLE
where addmonths(monthstart(date(today())),-1)>=Month;

Drop TABLE EPIC_HOURS_CROSSTABLE;

cbaqir · ‎2020-07-10

WHat did you have to change? Thank you!

cbaqir · ‎2020-07-10

Can you have more than 1 cross table in a qvw?