Troubleshooting 'Field not Found - <My Field

[Anonymous]

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

• 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!!

Thanks QV community!

[luismadriz · Accepted solution]

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

[Anonymous]

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!!

[luismadriz]

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,

[Anonymous]

Of course! Just did.

Thank you!

[luismadriz]

Thanks Mona,

Please mark one of the replies as Correct.... The green star, that way the thread will be resolved,

Regards,

Luis

[Anonymous]

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?

[luismadriz]

Hi Mona,

If you want, send me your script and I'll make the changes,

Regards,

Luis

[Anonymous]

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!

[luismadriz]

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...