Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bhawna_agrawal
New Contributor II

How can I load data for multiple files where I joined the tables based on key

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.

10 Replies
qlikmeplease
Contributor III

Re: How can I load data for multiple files where I joined the tables based on key

Have you tried adding "LOAD * SQL" infront of your query statement?

bhawna_agrawal
New Contributor II

Re: How can I load data for multiple files where I joined the tables based on key

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.

JustinDallas
Valued Contributor II

Re: How can I load data for multiple files where I joined the tables based on key

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))

QlikView Quoteology

amit_gupta
New Contributor III

Re: How can I load data for multiple files where I joined the tables based on key

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.

bhawna_agrawal
New Contributor II

Re: How can I load data for multiple files where I joined the tables based on key

Thanks for your response. When I tried the solution provided I got the below error.

Error.png

amit_gupta
New Contributor III

Re: How can I load data for multiple files where I joined the tables based on key

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.

bhawna_agrawal
New Contributor II

Re: How can I load data for multiple files where I joined the tables based on key

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>';

amit_gupta
New Contributor III

Re: How can I load data for multiple files where I joined the tables based on key

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.

bhawna_agrawal
New Contributor II

Re: How can I load data for multiple files where I joined the tables based on key

I tried same query after some time and it worked. Thanks for your support.