topic Re: WHERE IN (previouslyLoadedTable.Field) in QlikView

WHERE IN (previouslyLoadedTable.Field)

Tue, 09 Apr 2013 11:24:37 GMT

I'm trying to restrict the number of records obtained through a SQL SELECT.

My code is something like this and it's working, but it is a bit redundant to restate the SELECT:

HEADER:

LOAD orderNumber,

orderDate;

SQL SELECT orderNumber,

orderDate,

FROM db.orderHeaderTable

WHERE orderDate BETWEEN date1 AND date2;

DETAILS:

LOAD orderNumber,

orderItem,

orderQty,

orderPrice;

SQL SELECT orderNumber,

orderItem,

orderQty,

orderPrice

FROM db.orderDetailsTable

WHERE orderNumber IN (SELECT orderNumber FROM db.orderHeaderTable WHERE orderDate BETWEEN date1 AND date2);

I would like to change the WHERE statement in the last SELECT using the previously loaded table, something like:

WHERE orderNumber IN (HEADER.orderNumber)

I can't get that to work, is it possible to do?

Re: WHERE IN (previouslyLoadedTable.Field)

Anonymous — Tue, 09 Apr 2013 12:39:12 GMT

You can use LEFT KEEP LOAD, or exists() function. Here is an example with existst():

HEADER:
     LOAD orderNumber,
               orderDate;
     SQL SELECT orderNumber,
               orderDate,
     FROM db.orderHeaderTable
     WHERE orderDate BETWEEN date1 AND date2;

     DETAILS:
     LOAD orderNumber,
               orderItem,
               orderQty,
               orderPrice
WHERE exists(orderNumber);
     SQL SELECT orderNumber,
               orderItem,
               orderQty,
               orderPrice
     FROM db.orderDetailsTable

Re: WHERE IN (previouslyLoadedTable.Field)

Tue, 09 Apr 2013 13:37:49 GMT

you can do an inner join on your first table instead of using Sub query and where clause, which will give you the correct result.

Re: WHERE IN (previouslyLoadedTable.Field)

gandalfgray — Tue, 09 Apr 2013 15:19:01 GMT

No, you cannot reference a QlikView table (HEADER in your case) in the SQL SELECT part.

The SQL SELECT is sent for execution to the relational database that you are reading data from, and that database has no idea what HEADER.orderNumber is.

Re: WHERE IN (previouslyLoadedTable.Field)

jagan — Tue, 09 Apr 2013 17:26:15 GMT

HI,

In Qlikview you can do this in load statement by using Except(), but you cannot directly do this in Select statement. Check Qlikview help file for example.

You can optimize your script like below

HEADER:

SQL SELECT orderNumber,

orderDate,

FROM db.orderHeaderTable

WHERE orderDate BETWEEN date1 AND date2;

DETAILS:

SQL SELECT OD.orderNumber,

OD.orderItem,

OD.orderQty,

OD.orderPrice

FROM db.orderDetailsTable OD

INNER JOIN db.orderHeaderTable OHD ON OHD.orderNumber = OD.orderNumber AND
orderDate BETWEEN date1 AND date2;

Hope this helps you.

Regards,

Jagan.

Re: WHERE IN (previouslyLoadedTable.Field)

Tue, 09 Apr 2013 18:18:08 GMT

Thank You GandalfGray for the answer and clarification.

Thank you all for your time, I appreciated the suggestion for the inner join.

Re: WHERE IN (previouslyLoadedTable.Field)

Anonymous — Tue, 09 Apr 2013 19:15:20 GMT

Maybe INNER join is acceaptable for you, but it can give you a wrong result. It eliminates Details row if there is no orderNumber in Header- but it also eliminates Header records in there is no orderNumber in Details. If this situationj is possible, use LEFT join (or left keep). Still, I recommend exists() function over the left join or left keep just because it is faster.

Regards,

Michael

Re: WHERE IN (previouslyLoadedTable.Field)

Tue, 09 Apr 2013 20:53:27 GMT

I'm really sorry Solomovich, I seen your first reply only now. I didn't want to ignore it.

I'm quite new to QlikVew and testing things out.

I thought about the LEFT join.

Can you please explain this a bit more?

Still, I recommend exists() function over the left join or left keep just because it is faster.

I'll try to explain how, at this time, I think it works:

- SQL to get the data from the DB;

- LOAD to load the data and make it usable to QlikView.

Maybe I'm wrong but, with performance in mind, I don't think is good to extract all the Details table from the DB (without the orderNumber/Data constraint done through the Header table) and only then exclude the records I don't need with the exists() function.

As you stated it would be faster for QV to filter it, but wouldn't it be a pretty good workload - time consuming - for the DBServer to dump all the data in the table? Wouldn't it be a waste of bandwidth to send millions of records to a client and then filter it out? The details table is easily some millions records.

As I said I'm new to QV, so maybe I didn't understand how to use the exists() function correctly.

It is best to correct errors early, I don't want to pickup bad habits.

Thank you in advance for you help,

edit: rephrase, grammar.

Re: WHERE IN (previouslyLoadedTable.Field)

Anonymous — Wed, 10 Apr 2013 14:24:40 GMT

Apparently the moderated mode introduced a lot of delays 😞

Let's start from your original question - how to load Details only if the Orders are loaded. If we do not change the overall structure, keeping Headers and Details separately, you can use either LEFT KEEP LOAD or exists. I showed the version with exists earlier. Here is how it would be with LEFT KEEP LOAD:

HEADER:
LOAD orderNumber,
orderDate;
SQL SELECT orderNumber,
orderDate,
FROM db.orderHeaderTable
WHERE orderDate BETWEEN date1 AND date2;

DETAILS:
LEFT KEEP (HEADER)
LOAD orderNumber,
orderItem,
orderQty,
orderPrice
WHERE exists(orderNumber);
SQL SELECT orderNumber,
orderItem,
orderQty,
orderPrice
FROM db.orderDetailsTable;

It will return the same result as version with exists(), loading the DETAILS rows only if the orderNumber value has been loaded into the data earlier (into any table). From what I know, it will be slower with the left keep than with exists().
I'm not sure on what level the data filtering works, but so far my impression is that only the records that are needed will be pulled form the database.

Now, how I'd do it in most cases. I'd rather join both tables into one on the SQL level:

ORDERS:
orderNumber,
orderDate
orderItem,
orderQty,
orderPrice
SQL SELECT
h.orderNumber,
h.orderDate
d.orderItem,
d.orderQty,
d.orderPrice
FROM db.orderHeaderTable h
LEFT OUTER JOIN db.orderDetailsTable d on d.orderNumber=h.orderNumber
WHERE h.orderDate BETWEEN h.date1 AND h.date2;

I undersatnd that there could be some consequences, like multiple records with the same orderNumber, that should be dealt with later in the script or on the front end if necessary.

And, the last suggestion. If you must keep HEADER and DETAILS as separate tables, and at the same time the database workload has high priority, repeat the conditions it more or less the same way as you do now. Except, I prefer this syntax:

HEADER:
LOAD
orderNumber,
orderDate;
SQL SELECT
orderNumber,
orderDate,
FROM db.orderHeaderTable
WHERE orderDate BETWEEN date1 AND date2;

DETAILS:
orderNumber,
orderItem,
orderQty,
orderPrice
SQL SELECT
h.orderNumber,
d.orderItem,
d.orderQty,
d.orderPrice
FROM db.orderHeaderTable h
LEFT OUTER JOIN db.orderDetailsTable d on d.orderNumber=h.orderNumber
WHERE h.orderDate BETWEEN h.date1 AND h.date2;

Looks like I didn't add anything new after all... 🙂

Regards,
Michael

Re: WHERE IN (previouslyLoadedTable.Field)

Wed, 10 Apr 2013 22:41:49 GMT

Yes, I've to agree about the moderated mode ;(

Yesterday morning, after various tests, I ended up with the LEFT join; I'm not concerned about the multiple records with same order number, I need it that way for certain kinds of analysis.

About this:

I'm not sure on what level the data filtering works, but so far my impression is that only the records that are needed will be pulled form the database.

I will try that this morning, as I said my details table has some millions records in it so it would be pretty fast to check:)

I didn't thought about your last example, the one in my first post was pretty brutal with that subquery, I bet that yours is faster!

Thank you again for your time,