"File Not Found" in Resident Load data even though the Table Exists

[Elasimson]

Im facing a issue while working in the Qlik Analytics on my script . Im getting an error : 

The following error occurred:
Field 'downtimes_start_ts' not found

When trying to cretae a Resident Load from a table. However the table is already loaded in the script and i can see the data is the Load Table before i use the Resident Call.

My Script :

downtimes:
LOAD
host_name,
comment as downtimes_comment,
author as downtimes_author,
Num(scheduled_start_time) as downtimes_start_epoch,
Num(scheduled_end_time) as downtimes_end_epoch,
Timestamp(start_time/ 1000 / 86400 + 25569) as downtimes_start_ts,
Timestamp(end_time/ 1000 / 86400 + 25569) as downtimes_end_ts,
has_been_cancelled,
If(WildMatch(lower(trim(SubField(host_name, '.', 1))), 'bsux*', 'bsuh*', 'tlux*'), 'AIX',
If(WildMatch(lower(trim(SubField(host_name, '.', 1))), 'bsuo*'), 'iSeries',
If(WildMatch(lower(trim(SubField(host_name, '.', 1))), 'bsul*', 'fsdebsul*'), 'Linux',
If(WildMatch(lower(trim(SubField(host_name, '.', 1))), 'bsse*', 'fsdebsse*'), 'Windows',
If(WildMatch(lower(trim(SubField(host_name, '.', 1))), 'sap*', 'siem*', 'ora*', 'hdb*'), 'Application',
'Unknown'))))) as downtimes_os_type
FROM [lib://IH-IO Infrastructure Reporting - SSBI (Shared):DataFiles/downtimes_1.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

downtime_monthly_segments:
LOAD
host_name,
downtimes_start_epoch,
downtimes_end_epoch,
downtimes_start_ts,
downtimes_end_ts,
downtimes_comment,
downtimes_author,
Date(AddMonths(MonthStart(downtimes_start_ts), IterNo() - 1)) as month,
RangeMax(downtimes_start_ts, Date(AddMonths(MonthStart(downtimes_start_ts), IterNo() - 1))) as downtime_monthly_segment_start_ts,
RangeMin(downtimes_end_ts, Date(AddMonths(MonthStart(downtimes_start_ts), IterNo()))) as downtime_monthly_segment_end_ts,
(RangeMin(downtimes_end_ts, Date(AddMonths(MonthStart(downtimes_start_ts), IterNo())))
- RangeMax(downtimes_start_ts, Date(AddMonths(MonthStart(downtimes_start_ts), IterNo() - 1)))) * 24 * 60 * 60 as downtime_monthly_segment_duration_sec,
downtimes_comment as downtime_monthly_comment
RESIDENT downtimes
WHILE Date(AddMonths(MonthStart(downtimes_start_ts), IterNo() - 1)) < MonthStart(downtimes_end_ts);

[adilio_silva]

You can try replacing the while statement with something else.

Maybe a where statement will do the trick.

[marcus_sommer]

There are various reasons possible, for example:

• any qualifying in the script
• any aliasing to the fields
• the field or the table is dropped before

Beside this you may not need this monthly table else creating these information already within the source-load with a preceding-load, like:

load *, Expr3 as Month while Start + iterno() <= End;
load *, Expr1 as Start, Expr2 as End from csv;

[hugo_andrade]

Hey @Elasimson ,

Interesting question.

It looks like there's an issue while loading your downtimes table.

Could you add a row count after the table loads? 

It would be something like:

LET vdowntimesRows = NoOfRows('downtimes');
TRACE vdowntimesRows = $(vdowntimesRows);

That way you can troubleshoot if the table exists and has data after your initial load.

On a separate note, I had a script issue in the past caused by previous script sections. Would you try moving this piece of code to a brand new application, and let it run? That will help isolate the issue and assist in troubleshooting.

[Chanty4u]

Try this checks 

TRACE Fields in downtimes: $(TableNumber('downtimes'));

Or

Load below statement to check which columns being created 

First 10 Load * Resident downtimes;

And one more thing can you check 

the base fields (start_time, end_time)  exist in your CSV or not 

[howdash]

It looks like the issue is not with a missing column but rather with an improper use of the DO...WHILE loop.

@Elasimson what are you trying to do with the DO...WHILE loop?

[marcus_sommer]

It's not an external do-while-loop as a control-statement on the outside from any loads/statements else an inner-load-loop calling each record n times (fixed or dynamically calculated).

It's a very powerful feature which could be also used several times and/or combined with autogenerate and/or subfield() within preceding-chains to create generic data in a very performant way.

[Adam_Romanowski]

Shouldn't be

WHILE Date(AddMonths(MonthStart(downtimes_start_ts), IterNo() - 1)) <= MonthStart(downtimes_end_ts);

---

?