Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II
‎2020-07-10 11:18 AM

Date# Value format error

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;

 

Labels (3)
Labels
Preview file
323 KB
EPIC CAPACITY Willow.qvw
2,219 Views
0 Likes
1 Solution

Accepted Solutions
sunny_talwar
MVP
MVP
‎2020-07-10 01:48 PM

In response to cbaqir

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

2,015 Views
10 Replies
sunny_talwar
MVP
MVP
‎2020-07-10 11:25 AM

Are you doing a CrossTable Load by any chance?

2,087 Views
Kushal_Chawda
MVP
MVP
‎2020-07-10 11:25 AM

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

2,087 Views
cbaqir
Specialist II
Specialist II
‎2020-07-10 12:18 PM
Author

In response to sunny_talwar

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

2,052 Views
0 Likes
cbaqir
Specialist II
Specialist II
‎2020-07-10 12:18 PM
Author

In response to Kushal_Chawda

Added to original post.

2,051 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-07-10 01:27 PM

In response to cbaqir

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.

2,025 Views
0 Likes
cbaqir
Specialist II
Specialist II
‎2020-07-10 01:35 PM
Author

In response to sunny_talwar

I'm still getting error. See attached QVW.

EPIC CAPACITY Willow.qvw
2,019 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-07-10 01:48 PM

In response to cbaqir

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;
2,016 Views
cbaqir
Specialist II
Specialist II
‎2020-07-10 02:35 PM
Author

In response to sunny_talwar

WHat did you have to change? Thank you!

2,009 Views
0 Likes
cbaqir
Specialist II
Specialist II
‎2020-07-10 03:35 PM
Author

In response to sunny_talwar

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

2,005 Views
0 Likes