Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Hi,
Try using preceding load. it will fix the issue.
Regards
kc
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.
test with the resident load immediately after the sql load, rule out any dropped tables or similar
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?
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;
Hi,
Which database are you using in the back end ? I suspect this can be an ODBC driver issue.
Thanks
Amit
Hi Eddie,
That failed too with :-
Field not found - <ddf>
SQL
Select
fwk_num
FROM
dw.CALENDAR
where fwk_num = 201422
why your field FWK_NUM from qvd is uppercase?
when you do just a sql select .....
you get FWK_NUM or fwk_num ?
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.
Oracle 11.2.0.3
I'm using the 64bit instant client ODBC driver on Windows 8.1