Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tiklam_dynasys
Contributor

Qlik Sense Data Load Syntax

Hi All,

     Can someone provide a help on converting the query below so that it can be used in Qlik Sense Data Load Editor??

select CustomerID, CompanyName
from customers as a
where exists
(
  
select * from orders as b
  
where a.CustomerID = b.CustomerID
  
and ShipCountry = 'UK'
);

Best Regards,

Louis

7 Replies
MVP
MVP

Re: Qlik Sense Data Load Syntax

If you are creating a DB connection in QlikSense, you might be able to just use the SQL code in your script.

Have you tried this?

If you are only working with other sources (e.g. QVD table files), but you want to create the same result,maybe try

TMP:

LOAD DISTINCT

          CustomerID as CustomerIDShippedUK

FROM YourOtherOrderTableSource

WHERE ShipCountry = 'UK';

Customer:

LOAD CustomerID, CompanyName

FROM YourOtherCustomerTableSource

WHERE EXISTS(CustomerIDShippedUK, CustomerID);

If you are sure that CustomerID hasn't loaded before in your LOAD script, you can also just use CustomerID:

TMP:

LOAD DISTINCT

          CustomerID

FROM YourOtherOrderTableSource

WHERE ShipCountry = 'UK';

Customer:

LOAD CustomerID, CompanyName

FROM YourOtherCustomerTableSource

WHERE EXISTS(CustomerID);

Then

DROP TMP;

MVP
MVP

Re: Qlik Sense Data Load Syntax

Like Stefan already mentioned, you might just use the SQL Query in Qlik Sense:

TableName:

LOAD *; // Optional

select CustomerID, CompanyName
from customers as a
where exists
(
 
select * from orders as b
 
where a.CustomerID = b.CustomerID
 
and ShipCountry = 'UK'
)
;

You can add a preceding load (Preceding Load) if you wish to do some manipulation in QlikView itself, but I think it makes a lot of sense to use Where in SQL because then you are weeding out rows directly into the database and bringing only those which are needed.

tiklam_dynasys
Contributor

Re: Qlik Sense Data Load Syntax

38

Untitled 2.png

Untitled 3.png

I have no idea what's wrong with it

Table and column are existing

MVP
MVP

Re: Qlik Sense Data Load Syntax

Have you tried running the same exact query outside of QlikView? With SQL, I usually run the query in Toad and just copy paste the code into QlikView and has always worked. Can you try doing the same?

tiklam_dynasys
Contributor

Re: Qlik Sense Data Load Syntax

Screen Shot 2016-05-25 at 4.31.52 pm.png

I try on phpMyAdmin and it works fine.

MVP
MVP

Re: Qlik Sense Data Load Syntax

What if you do Select * instead of SELECT CONTNO, CONTSQ?

tiklam_dynasys
Contributor

Re: Qlik Sense Data Load Syntax

Finally, it works.

I make a stupid mistake.

Thank you for reply!!!