I'm new to QV and exploring my way around. I've learned several tricks so far and I'm hoping someone can help me with a minor issue.
Twice now, after I've created a new field in one of a few tables I've loaded and attempted to re-format or include in downstream calculations, I get the Script Error: 'Field Not Found - <My Field>.
The examples are
The first new field that I created was a result of a mapped item and formatted as a date (Renewed On 2). When I attempted to convert this to fiscal quarter with this code, the script failed:
Key_Table:
Mapping LOAD [Old Entitlement Key] as [Entitlement Key],
[Sales Order Date]
FROM ....excel file
All_Data:
LOAD [Bill To],
[End User],
[Old Entitlement Key],
[Item: Name],
[Entitlement Key],
ApplyMap('Key_Table',[Entitlement Key],'') as [Renewed On 2],
Year([Renewed On 2])&'-'&'Q'& Ceil(Month([Renewed On 2])/3) as [Renewed On Quarter 3]
FROM ....excel file
The second attempt was incorporating the newly calculated field (Actual duration) into script in order to create various categories in years as a new field:
([End Date]-[Start Date])/365 as [Actual Duration],
IF([Actual Duration]<1.5,"1 Year",IF([Actual Duration]<2.5,"2 Years",IF([Actual Duration]<3.5,"3 Years",IF([Actual Duration]<4.5,"4 Years",IF([Actual Duration]<5.5,"5 Years",IF([Actual Duration]>5.5,"> 6 Years")))))) as term
And here are my failed attempts for resolution are:
Changing data format to date or number respectively
Forcing date change using Makedate function
Joining tables
Any recommendations or insights would be appreciated!!
