Problem with fields using resident

[#1]

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;

[jyothish8807]

Hi,

Try using preceding load. it will fix the issue.

Regards

kc

[#3]

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.

[tcullinane]

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

[#5]

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?

[#6]

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;

[#7]

Hi,

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

Thanks

Amit

[#8]

Hi Eddie,

That failed too with :-

Field not found - <ddf>

SQL

Select

fwk_num

FROM

  dw.CALENDAR

  where fwk_num = 201422

[maxgro]

why your field   FWK_NUM   from qvd is uppercase?

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

you get FWK_NUM   or   fwk_num   ?

[#10]

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.

[#11]

Oracle 11.2.0.3

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