Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi QV Community!
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
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
And here are my failed attempts for resolution are:
Any recommendations or insights would be appreciated!!
Thanks QV community!
Hi,
You can't use the calculated field within the same pass. Instead of using [Renewed on 2], keep using the mapping.. Same thing for Actual Duration...
Or, make another pass (preceding load) use the calculated fields,
I hope this helps,
Cheers,
Luis
Hi,
You can't use the calculated field within the same pass. Instead of using [Renewed on 2], keep using the mapping.. Same thing for Actual Duration...
Or, make another pass (preceding load) use the calculated fields,
I hope this helps,
Cheers,
Luis
Thanks Luis!
It worked! I had to plug in the entire formula:
[End Date]-[Start Date])/365
However not sure what you mean about the same/another pass.
Does this mean that I would start an other load statement by introducing the value as the same value:
ie: Load [Actual Duration] as [Actual Duration]
Or reintroduce : ApplyMap('Key_Table',[Entitlement Key],'') as [Renewed On 2], in the next calculation.
Can you clarify how one can redefine a newly created field and then use in future calculations?
Thanks again Luis!!
No problem,
Please remember to mark as Correct and Helpful
For example:
Load
*,
c*10 as d;
Load
a,
b,
a+b as C
from ...;
Well, evemn better if you check this one: Loading data from a previously loaded table ‒ QlikView
Cheers,
Of course! Just did.
Thank you!
Thanks Mona,
Please mark one of the replies as Correct.... The green star, that way the thread will be resolved,
Regards,
Luis
Thanks Luis.
Unfortunately I have not been able to resolve the field. The script error using the recommendation provided in here Loading data from a previously loaded table ‒ QlikView shows 'Table not found'.
Also, I'm not clear on the example you've shown above d.
how does d get used in calculations in your second load table?
Hi Mona,
If you want, send me your script and I'll make the changes,
Regards,
Luis
Hello Luis,
Once again thanks for your help! I may have spent an entire day watching youtube videos, searching QV help and trying a few scripts...
The script that I'm working on is below and I've highlighted the problematic statements.
Key_Table:
Mapping LOAD [Old Entitlement Key] as [Entitlement Key],
[Sales Order Date]
FROM
[End User],
[Item: Name],
Quantity,
[Amount (Net)],
[Old Entitlement Key],
[Sales Order Date],
[Start Date],
[End Date],
[Entitlement Key],
[License Type],
Region,
[Renewed on],
Year([End Date])&'-'&'Q'& Ceil(Month([End Date])/3) as [Renewal Quarter 3],
Year([Sales Order Date])&'-'&'Q'& Ceil(Month([Sales Order Date])/3) as [Sales Order Quarter 3],
ApplyMap('Key_Table',[Entitlement Key],'') as [Renewed On 2],
Year(ApplyMap('Key_Table',[Entitlement Key],''))&'-'&'Q'& Ceil(Month(ApplyMap('Key_Table',[Entitlement Key],''))/3) as [Renewed On 3],
Year([Renewed On 2])&'-'&'Q'& Ceil(Month([Renewed On 2])/3) as [Renewed On Quarter 3],
([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
FROM
[C:\Us...
Again, I can't thank you enough. I wish that I can send you extra points if it's of any help at all! Let me know.
Have an awesome day!
No problem Mona, I keep learning every day,
This is what I mean. The first pass happens closer to where the "FROM [C\Us... " is, and it's here when [Renewed on 2] and [Actual Duration] are created, then, in the second pass which is above (this took me a while to understand when I started with Qlik few months ago), is when you load everything if you want, thus the * and add the new calculated fields [Renewed On 3], term, etc,
Of course you could have done all in one pass as you did with the mapping. The decision may come down to performance, number of records, maintainability, elegancy... etc.
Please review the idea and happy to discuss if you have more questions
Cheers,
Luis
All_Data:
LOAD *,
Year([Renewed On 2])&'-'&'Q'& Ceil(Month([Renewed On 2])/3) as [Renewed On 3],
Year([Renewed On 2])&'-'&'Q'& Ceil(Month([Renewed On 2])/3) as [Renewed On Quarter 3],
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;
LOAD [Bill To],
[End User],
[Item: Name],
Quantity,
[Amount (Net)],
[Old Entitlement Key],
[Sales Order Date],
[Start Date],
[End Date],
[Entitlement Key],
[License Type],
Region,
[Renewed on],
Year([End Date])&'-'&'Q'& Ceil(Month([End Date])/3) as [Renewal Quarter 3],
Year([Sales Order Date])&'-'&'Q'& Ceil(Month([Sales Order Date])/3) as [Sales Order Quarter 3],
ApplyMap('Key_Table',[Entitlement Key],'') as [Renewed On 2],
([End Date]-[Start Date])/365 as [Actual Duration]
FROM
[C:\Us...