Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have written a query in SQL database where multiple tables are used and they are joined based on keys. This query is working fine in oracle DB. I know that pasting same query in loading script will not work. Please suggest.Below is the SQL query for which data is excepted to be loaded.
select usr.usr_login, act.act_name, usr.usr_start_date, app_instance.app_instance_name, ost.ost_status, oiu.oiu_create, usr.usr_create from usr, oiu, ost, act, app_instance where
usr.usr_key = oiu.usr_key and
oiu.ost_key = ost.ost_key and
usr.act_key = act.act_key and
app_instance.app_instance_key = oiu.app_instance_key and
act.act_name in ('<XXX>') and
to_date(usr.usr_create ,'DD-MON-YY') >= '01-OCT-18'
Thanks in advance.
Have you tried adding "LOAD * SQL" infront of your query statement?
I had tried something as below:
LOAD ACT_KEY,
ACT_NAME;
LOAD APP_INSTANCE_KEY,
APP_INSTANCE_NAME;
LOAD OIU_KEY,
USR_KEY,
OIU_CREATE;
LOAD OST_KEY,
OST_STATUS;
LOAD USR_KEY,
ACT_KEY,
USR_LOGIN,
USR_START_DATE,
USR_CREATE;
SQL:<query as mentioned above>
here I added LOAD for all the tables and then the SQL statement.
You say the script "won't work". What do you mean by that? Is it giving you an error on the QlikSense side, or an error on the SQL side? I can see that you have some single quoted values in your SQL query, and most of the time, Qlik doesn't like those. In my codebase I have the same problem and I've solved it like this.
WHERE o.ord_status = ' & chr(39) & 'CMP' & chr(39))
Hi Bhawna,
I think one load statement is more than enough.
Try like this:
LOAD
ACT_KEY,
ACT_NAME
APP_INSTANCE_KEY,
APP_INSTANCE_NAME,
OIU_KEY,
USR_KEY,
OIU_CREATE,
OST_KEY,
OST_STATUS
USR_KEY,
ACT_KEY,
USR_LOGIN,
USR_START_DATE,
USR_CREATE;
SQL select usr.usr_login, act.act_name, usr.usr_start_date, app_instance.app_instance_name, ost.ost_status, oiu.oiu_create, usr.usr_create from usr, oiu, ost, act, app_instance where
usr.usr_key = oiu.usr_key and
oiu.ost_key = ost.ost_key and
usr.act_key = act.act_key and
app_instance.app_instance_key = oiu.app_instance_key and
act.act_name in ('<XXX>') and
to_date(usr.usr_create ,'DD-MON-YY') >= '01-OCT-18';
Also make sure your connection is working.
In case of Qliksense you need to write/Paste LIB connection statement in script before load statement.
Something like this
LIB CONNECT TO 'SQLdb';
Hope it will work for you.
Thanks for your response. When I tried the solution provided I got the below error.
Yes, Possibly there are 2 reasons for that.
1. Field's name are case sensitive, it should be same in both Load statement as well as SQL Queries.
2. If you are using data from multiple SQL tables, you use tablename.fieldname, you should rename it, then use same name in load statement.
for example:
Load Time,
Actual;
SQL SELECT A.time as Time, Count(B.value) as Actual
FROM A
Left join B
on A.ID=B.ID;
Try like this.
I believe, it will work.
Hi Amit,
Thanks for your prompt response but I am still getting issue. I tried join with a small query. Below is the script I tried to load data. I executed it in debug mode and found that it failed with error "UserLogin not found" at point highlighted in bold.
Load UserLogin,
BUName;
SQL
SELECT usr.usr_login as UserLogin, act.act_name as BUName from usr left join act
on
usr.act_key = act.act_key and
usr.usr_login ='<xyz>';
Have you included Connection:
LIB CONNECT TO 'SQLdb';
If it is not working even after including Connection statement , It is probability a Connection issue, or it is not accessing the data.
Please check & test connection.
I am following the same way, it is working with me fine.
I tried same query after some time and it worked. Thanks for your support.