SQL Import with "Where Exists" based on another table

[chris1987]

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

[Oleg_Troyansky]

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.

[t_chetirbok]

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

[maxgro · Accepted solution]

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

[chris1987]

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

[chris1987]

Worked Perfectly, thanks for your help!

[chris1987]

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