topic Re: Problem with fields using resident in QlikView

Problem with fields using resident

Thu, 25 Sep 2014 14:55:23 GMT

Hi, Sorry, I have lots of SQL experience but new to Qlik.

I have a script that loads from an odbc connection and I want to then use it in a load using resident.

Even the simple one below gives a script error as

Field not found - <fwk_num>

trade_data:

LOAD

fwk_num

RESIDENT trade_trans

What am I doing wrong?

I tried the unqualify command that I found when looking up these type of errors but it too didnt work.

I am using the free trial version at the minute as my company is still in the process of buying licences.

Is it a restriction of the trial version?

Script after the ODBC connect string is shown below.

Thanks for reading.

trade_trans:

Select

fwk_num

FROM

dw.CALENDAR;

trade_data:

LOAD

fwk_num

RESIDENT trade_trans;

store trade_data into 201422_trade.qvd;

Re: Problem with fields using resident

datanibbler — Thu, 25 Sep 2014 14:59:35 GMT

Hi Ian,

your LOAD looks all right - did you close it off with a semicolon like the others?

Re: Problem with fields using resident

Thu, 25 Sep 2014 15:02:09 GMT

Yes.

Re: Problem with fields using resident

maxgro — Thu, 25 Sep 2014 15:05:13 GMT

check this script first

trade_trans:

Select

fwk_num

FROM

dw.CALENDAR;

exit script;

use table viewer CTRL+T to see records and fields of trade_trans

Re: Problem with fields using resident

Thu, 25 Sep 2014 15:11:57 GMT

Yeah, That works. But then I had already seen data loaded and the execution progress window shows data rows being processed.

I have tried using an alias but that fails to find it too!

Annoying as it must be something really silly I'm missing.

You dont have to close down the odbc connection before using resident do you?

Re: Problem with fields using resident

DavidFoster1 — Thu, 25 Sep 2014 15:14:06 GMT

I think you might be experiencing auto-concatenate problems. If tables have the same fields then they will be merged together.

Try again but this time use

trade_data:

LOAD

fwk_num as fwk_num_tmp

RESIDENT trade_trans;

Re: Problem with fields using resident

maxgro — Thu, 25 Sep 2014 15:17:58 GMT

no problem with odbc

I think auto concat as David suggests, use David post or this

trade_data:

noconcatenate

LOAD

fwk_num

RESIDENT trade_trans;

Re: Problem with fields using resident

Thu, 25 Sep 2014 15:22:33 GMT

Sorry guys, Neither noconcatenate or the alias on the load from resident make any difference.

Re: Problem with fields using resident

Thu, 25 Sep 2014 15:26:12 GMT

Hello,

As you are loading same field in Resident load, I would recommend using preceding load.

trade_trans:

Load fwk_num;

SQL Select

fwk_num

FROM

dw.CALENDAR;

Re: Problem with fields using resident

maxgro — Thu, 25 Sep 2014 15:29:03 GMT

fwk_num

is lowercase?

or uppercase?

Re: Problem with fields using resident

jyothish8807 — Thu, 25 Sep 2014 15:38:25 GMT

Hi,

Try using preceding load. it will fix the issue.

Regards

Re: Problem with fields using resident

Thu, 25 Sep 2014 15:48:09 GMT

It could be concatenate issue, if its that Alias or Preceding Load should work or no data is getting loaded into that field.

Can you export that table data into excel and try loading that, instead of direct sql connection. If that still doesn't give you a clue on why its happening, please attach that excel and qvw here. I will see what I can do.

Re: Problem with fields using resident

tcullinane — Thu, 25 Sep 2014 16:01:41 GMT

test with the resident load immediately after the sql load, rule out any dropped tables or similar

Re: Problem with fields using resident

Fri, 26 Sep 2014 08:39:01 GMT

The load into resident will be an aggregation of the data in the first load.

I know I could do this in qlik, but I have my reason not to.

All I want to do is load an aggregated dataset from another.

I have tried a preceding load

ODBC CONNECT TO ...............

brtrans:

load ddf;

SQL

Select

fwk_num as ddf

FROM

dw.CALENDAR

where fwk_num = 201422;

tdata:

LOAD

ddf

RESIDENT brtrans;

But this now gives a script error

Field not found - <ddf>

SQL

Select

fwk_num as ddf

FROM

dw.CALENDAR

where fwk_num = 201422

If I take the alias away and use the original column name then the same error pops up.

As I said, the first load will work on its own and you can see data loaded.

I also built another, loading the first set into another qvd and then used this qvd as a source and it worked.

trade_trans:

LOAD FWK_NUM as ddf

FROM

(qvd);

adata:

LOAD

ddf

RESIDENT trade_trans;

It seems like an issue around using ODBC and resident in the same script.

Funny enough, if I use a preceding load on the single load to a qvd then it still fails on the variable being loaded, 'field not found'.

ODBC CONNECT TO ........

brtrans:

load ddf;

SQL

Select

fwk_num as ddf

FROM

dw.CALENDAR

where fwk_num = 201422;

store brtrans into 201422_tradet.qvd;

Doing a straight select works and loads the qvd.

ODBC CONNECT TO ........

brtrans:

Select

fwk_num as ddf

FROM

dw.CALENDAR

where fwk_num = 201422;

store brtrans into 201422_tradet.qvd;

So it looks like I am having issues defining variables.

Is there an environment variable that needs set somewhere?

Re: Problem with fields using resident

Fri, 26 Sep 2014 08:52:03 GMT

You have to give alias on the resident table column. Since first table and second table columns has the same name its resulting in the concatenate error.

Try this,

brtrans:

load ddf;

SQL

Select

fwk_num

FROM

dw.CALENDAR

where fwk_num = 201422;

tdata:

LOAD

fwk_num as ddf

RESIDENT brtrans;

Re: Problem with fields using resident

Fri, 26 Sep 2014 09:06:51 GMT

Hi,

Which database are you using in the back end ? I suspect this can be an ODBC driver issue.

Thanks

Amit

Re: Problem with fields using resident

Fri, 26 Sep 2014 09:13:11 GMT

Hi Eddie,

That failed too with :-

Field not found - <ddf>

SQL

Select

fwk_num

FROM

dw.CALENDAR

where fwk_num = 201422

Re: Problem with fields using resident

maxgro — Fri, 26 Sep 2014 09:14:45 GMT

why your field FWK_NUM from qvd is uppercase?

when you do just a sql select .....

you get FWK_NUM or fwk_num ?

Re: Problem with fields using resident

Fri, 26 Sep 2014 09:18:41 GMT

Yes, I remember database fields will be in Upper case. But, that should be throwing an error from the database. But, I'm talking about Oracle database, shouldn't exactly be the same for SQL server. But worth a try.

Re: Problem with fields using resident

Fri, 26 Sep 2014 09:20:16 GMT

Oracle 11.2.0.3

I'm using the 64bit instant client ODBC driver on Windows 8.1