topic Re: Field not found in QlikView

Field not found

master_student — Mon, 29 Aug 2016 08:42:10 GMT

Hi,

I have one qlikview loader file. when I am trying to reload it is showing Field Not Found but I can able to see that field present in source table. Could anyone help me why it is like that.

Part of the log file :

29/08/2016 09:32:30: 0017 Temp:

29/08/2016 09:32:30: 0018 LOAD Date(Date#(max_date,'YYYYMMDD'),'DD/MM/YYYY') as DWDate

29/08/2016 09:32:30: 0019 select max(reference_date) as max_date

29/08/2016 09:32:30: 0020 From ...."

29/08/2016 09:32:30: 0021 WHERE chain_id in('L_Aggregate_KPI') and chain_status in('Completed Successfully')

29/08/2016 09:32:30: Error: Field not found - <max_date>

29/08/2016 09:32:30: General Script Error

29/08/2016 09:32:30: Execution Failed

29/08/2016 09:32:30: Execution finished.

sql query :

Thanks.

Re: Field not found

MindaugasBacius — Mon, 29 Aug 2016 08:48:42 GMT

Are those fields some how related?

I guess you do not have max_date in your Database but you're using it in Date#(max_date). That could be a reason.

You may see max_date field after executing the sentence because you have written:

select max(reference_date) as max_date.

Re: Field not found

mayankraoka — Mon, 29 Aug 2016 08:50:45 GMT

Hi Wiem,

Check using max_date column as max(reference_date)

If the loading statement is same you have to use original name rather then alialsing

Regards,

Mayank

Re: Field not found

master_student — Mon, 29 Aug 2016 09:02:07 GMT

I don't have max_date in my data base, I have reference_date but I am renaming it into max_date.

select max(reference_date) as max_date

Re: Field not found

Anil_Babu_Samineni — Mon, 29 Aug 2016 09:05:36 GMT

Are you using preceding load?

Re: Field not found

mayankraoka — Mon, 29 Aug 2016 09:05:51 GMT

Hi,

Can you please share your app or script image here?

Regards,

mayank

Re: Field not found

MindaugasBacius — Mon, 29 Aug 2016 09:07:17 GMT

Then instead of:

Date(Date#(max_date,'YYYYMMDD'),'DD/MM/YYYY') as DWDate

Try this:

Date(Date#(max(reference_date),'YYYYMMDD'),'DD/MM/YYYY') as DWDate

Re: Field not found

master_student — Mon, 29 Aug 2016 09:13:48 GMT

what do you mean by preceding load please?

Thanks

Re: Field not found

master_student — Mon, 29 Aug 2016 09:18:56 GMT

yes but it used to work correctly why this error appears?

Re: Field not found

mayankraoka — Mon, 29 Aug 2016 09:41:12 GMT

Hi Wiem,

Please refer below blog for Preceding load.This will help you.

https://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load

Regards,

Mayank

Re: Field not found

master_student — Mon, 29 Aug 2016 09:55:14 GMT

I didn't create the qvw file but after reading this blogPreceding Load

I can confirm that the preceding load is used.

Thanks

Re: Field not found

sasiparupudi1 — Mon, 29 Aug 2016 10:04:17 GMT

You are missing a group by clause .Please check your sql if it actually returning the max value or not

Re: Field not found

mayankraoka — Mon, 29 Aug 2016 11:18:07 GMT

Hi Wiem,

When you are Loading table without preceding load will not allow you to use QlikView functions.check “Preceding load” and see how the load script changes.This should solve your issue.

Regards,

Mayank

Re: Field not found

svinnakota — Mon, 29 Aug 2016 18:34:05 GMT

Hi,

Just call the column from the sql and apply max in Load Statement.

Ex:

Load max(reference_Date) asMax_date

SQL : Select reference_date from ....

where .....

And while using Aggr function on a field the format changes but don't worry about that. For comparison it works fine.

And if possible share the Script in text file with dummy data or at least the code which you tried. Let me know your comments.

Re: Field not found

rwunderlich — Mon, 29 Aug 2016 19:20:56 GMT

Your DB system is probably returning max_date as upper case MAX_DATE. Try to reference it as MAX_DATE in the LOAD statement.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Field not found

maxgro — Mon, 29 Aug 2016 19:20:57 GMT

remove the load .....

and just try

Temp:

select max(reference_date) as max_date

from ...

where ....;

exit script;

open the table editor (ctrl-t); is there a Temp table with a max_date field (or maybe MAX_DATE) ?