topic Field Not Found in QlikView

Field Not Found

Tue, 28 Aug 2012 10:13:53 GMT

Hi,

I have an issue with my load script. When I attempt to re-load the following messages occur saying:

Field not found <fixcode>

TMP: Load *,recno() as ID INLINE[Logdate,closedate}

When I press ok, I get the next error message:

Table not found

LOAD ID,

daystart(logdate)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(logdate),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(logdate)+iterno()-1=daystart(closedate), rangemax(maketime(8),rangemin(frac(closedate),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(closedate) >= daystart(logdate)+iterno()-1

When I press ok again, the next error message occurs:

Table not found
left join (INPUT)
LOAD

ID,
interval(sum(End-Start)) as Duration
Resident TMP where WeekDay(Date)<5 and not match(Date,41130,41140) group by ID

and finally the following message occurs saying:

Table not found

DROP TABLES statement

Attached in a notepad format is the load script I am using.

If I remove anything after the TMP and before the select statement, it works perfectly. Would someone be able to assist me with this issue please?

Re: Field Not Found

Anonymous — Tue, 28 Aug 2012 10:23:25 GMT

I can see that you create a field ID in the preceeding load, but there is ID already in the * - so you get two fields with the same name.

TMP:

LOAD *, recno() as ID INLINE [logdate, closedate];

LOAD ID,

daystart(logdate)+iterno()-1 as Date,

...

Regards,

Michael

PS: Just another reason to avoid LOAD * or SELECT *

Re: Field Not Found

jagannalla — Tue, 28 Aug 2012 10:39:15 GMT

Hello,

Lot of mistakes are there in your file.

1. Field not found <fixcode>

LOGINFO table is not loading from any datasource i.e. not from sql or qvd or txt file. If it is loading from sql the code should look like this

LOGINFO:
LOAD *, [logdate] as Logdate_Month,
                    Year([logdate]) as Logdate_Year
                    ,num(today()) - 1460 as test123
                    ,num([logdate]) as logdatetest
                    , if(MID(fixcode,11,4)='RCL0','Oracle'.......
.....
)))))))))))))))))))))))))))))))))) AS SOFTWARE_TEST
                    where num(today()) - 1460 < [logdate];
SQL Select * from Table1;

2. Table Not Found

There is no INPUT table is loaded in your qvw file. How can you use in resident load.

LOAD ID,
daystart(logdate)+iterno()-1 as Date,
if(iterno()=1, rangemin(rangemax(frac(logdate),maketime(8)),maketime(18)), maketime(8)) as Start,
....
....
Resident INPUT

3. You can't drop the tables which are not loaded in the qvw file.

Hope it helps you

Cheers!

Jagan

Re: Field Not Found

Tue, 28 Aug 2012 13:03:52 GMT

Hi Jagan,

The code you mentioned in your first point does work correctly when I enter it as you have just entered it. However what I am trying to achieve with the second point, is to create a column that works out the time difference between two fields that does not calculate between 08:00-18:00 on a daily basis and does excludes weekends.