topic Re: SQL Import with "Where Exists" based on another table in QlikView

SQL Import with "Where Exists" based on another table

chris1987 — Tue, 19 Jan 2016 16:38:38 GMT

Hi,

I’m new to Qlikview but I’m fairly comfortable with SQL and wondered if anyone can offer any help with the below issue I’m getting. I've browsed the forum but can't seem to find exactly what I'm looking for.

Here's the info:

- My db name is ttukdoor

- I have a table called “Order” which contains various fields, but the one that is relevant in the below example is “Order-no”

- I also have another table called “Order-line” which contains order line information, but has the common field name “Order-no”.

- On my form I have two calendar boxes where I set the variable $(startDate) and $(endDate)

What I want to do is pull the information from “Order” (which is working fine) and then pull the relevant records from “Order-line”

In SQL I’d usually do something like:

SELECT *

FROM Order

WHERE EXISTS (SELECT *

FROM Order-line

WHERE Order.Order-no = Order-line.Order-no);

I’m trying to replicate this in my .qvw file but I can’t get the method/syntax right, Here’s my .qvw file:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//This is the connection script and contains Save Path Variable
$(Include=..\includes\DBConnect.qvs);

//Set the correct date format for the db
LET startDate = date($(startDate),'YYYY-MM-DD');
LET endDate = date($(endDate),'YYYY-MM-DD');

OrderTable:
FIRST 1000 //For testing purposes
SQL
SELECT "In-entity","Order-no","Charge-cust","Order-date","Request-date","Qty-open-ord","Charge-name","Open-value"
FROM TTUKDOOR.PUB."order" WHERE "In-Entity" ='UD' AND "Order-date" BETWEEN '$(startDate)' and '$(endDate)';

STORE OrderTable INTO $(devPathQVD)OrderTable.qvd (qvd);

OrderLineTable:
SQL
SELECT "Order-no","Line-no","Item-no","Description"
FROM TTUKDOOR.PUB."order-line"
WHERE EXISTS (OrderTable."Order-no" = "Order-no");

STORE OrderLineTable INTO $(devPathQVD)OrderLineTable.qvd (qvd);

Any Help would be appreciated

Cheers

Chris

Re: SQL Import with "Where Exists" based on another table

Oleg_Troyansky — Tue, 19 Jan 2016 17:31:47 GMT

Hi Chris,

the standard SQL syntax uses the keyword IN in places of Exists (the latter is actually a QlikView function). So, in order to solve the problem in SQL, you should use IN:

SELECT

FROM

Order_Line

WHERE

OrderID IN (Select OrderID FROM Orders ...)

;

In a QlikView Load statement, the same could be done with Exists:

LOAD

FROM/Resident ...

WHERE

Exists(OrderID)

;

Or, you could do the same using RIGHT KEEP:

RIGHT KEEP (Orders)

LOAD

FROM/Resident ...

;

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy.

Re: SQL Import with "Where Exists" based on another table

t_chetirbok — Tue, 19 Jan 2016 17:51:12 GMT

Hello!

Try this:

OrderTable:

load * WHERE In-Entity ='UD' AND Order-date>=$(startDate) and Order-date<=$(endDate);
SQL
SELECT In-entity,Order-no,Charge-cust,Order-date,Request-date,Qty-open-ord,Charge-name,Open-value
FROM TTUKDOOR.PUB.order ;
STORE OrderTable INTO $(devPathQVD)OrderTable.qvd (qvd);

OrderLineTable:

load * WHERE EXISTS(Order-no);

SQL
SELECT Order-no, Line-no,Item-no,Description
FROM TTUKDOOR.PUB.order-line;
STORE OrderLineTable INTO $(devPathQVD)OrderLineTable.qvd (qvd);

Re: SQL Import with "Where Exists" based on another table

maxgro — Tue, 19 Jan 2016 18:04:39 GMT

I think is better to join the 2 order tables on the DBMS to get the lines, above all if you have a lot of data in the db and you want few data in Qlik

OrderTable:

SQL
SELECT "In-entity","Order-no","Charge-cust","Order-date","Request-date","Qty-open-ord","Charge-name","Open-value"
FROM TTUKDOOR.PUB."order" WHERE "In-Entity" ='UD' AND "Order-date" BETWEEN '$(startDate)' and '$(endDate)';

OrderLineTable:

SQL
SELECT ol."Order-no", ol."Line-no", ol."Item-no", ol."Description"
FROM

TTUKDOOR.PUB."order-line" ol,

TTUKDOOR.PUB."order" o

WHERE

o."In-Entity" ='UD' AND o."Order-date" BETWEEN '$(startDate)' and '$(endDate)'

and ol."Order-no" = o."Order-no";

Re: SQL Import with "Where Exists" based on another table

chris1987 — Wed, 20 Jan 2016 09:33:27 GMT

Hi, Thanks for the reply, although this did work, the db I was extracting info from is a very large db so I've opted for the below answer.

Cheers

Chris

Re: SQL Import with "Where Exists" based on another table

chris1987 — Wed, 20 Jan 2016 09:33:50 GMT

Worked Perfectly, thanks for your help!

Re: SQL Import with "Where Exists" based on another table

chris1987 — Wed, 20 Jan 2016 09:36:42 GMT

You are correct with the SQL (my error, I was jumping between a few different scripts when typing the original post). I'll make a note of the above, I'm sure it will come in useful!

Cheers

Chris