Qlik Sense Data Load Syntax

[Anonymous]

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

[swuehl]

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;

[sunny_talwar]

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.

[Anonymous]

38

I have no idea what's wrong with it

Table and column are existing

[sunny_talwar]

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?

[Anonymous]

I try on phpMyAdmin and it works fine.

[sunny_talwar]

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

[Anonymous]

Finally, it works.

I make a stupid mistake.

Thank you for reply!!!