topic Re: Date# Value format error in QlikView

Date# Value format error

cbaqir — Sat, 16 Nov 2024 00:20:52 GMT

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;

Re: Date# Value format error

sunny_talwar — Fri, 10 Jul 2020 15:25:51 GMT

Are you doing a CrossTable Load by any chance?

Re: Date# Value format error

Kushal_Chawda — Fri, 10 Jul 2020 15:25:53 GMT

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

Re: Date# Value format error

cbaqir — Fri, 10 Jul 2020 16:18:00 GMT

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

Re: Date# Value format error

cbaqir — Fri, 10 Jul 2020 16:18:19 GMT

Added to original post.

Re: Date# Value format error

sunny_talwar — Fri, 10 Jul 2020 17:27:57 GMT

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;

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.

Re: Date# Value format error

cbaqir — Fri, 10 Jul 2020 17:35:48 GMT

I'm still getting error. See attached QVW.

Re: Date# Value format error

sunny_talwar — Fri, 10 Jul 2020 17:48:18 GMT

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;

Re: Date# Value format error

cbaqir — Fri, 10 Jul 2020 18:35:22 GMT

WHat did you have to change? Thank you!

Re: Date# Value format error

cbaqir — Fri, 10 Jul 2020 19:35:37 GMT

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

Re: Date# Value format error

sunny_talwar — Sat, 11 Jul 2020 03:29:40 GMT

Yes you can have multiple CrossTables in a single qvw