topic Subselect in sql script in QlikView

Subselect in sql script

Anonymous — Wed, 18 Jan 2012 14:49:31 GMT

Hi all,

With this problem I need a subselect I think.

Let me give you an illustration of the problem: imagine we store our products in a storehouse. Product A120 is stored in storehouse 10, as you can see below. I would like to get the number 10 also in the row of AD121, because it has the jointproduct: AD120. Does anyone know how to perform this?

The problem is:

PRODUCTID	JOINTPRODUCT	STOREHOUSEID
AD120	-	10
AD121	AD120	-

My SQL looks like:

SELECT

product.productid

product.jointproduct

storehouse.storehouseid

FROM

product,

storehouse

WHERE

product.id = storehouse.productid;

As I said, I think a subselect can solve the problem but I don't know how to perform that.

I hope someone can help me out.

Kind regards,

Henco

Subselect in sql script

Wed, 18 Jan 2012 19:01:28 GMT

Hi Henco,

it seems that table can be reorganized in QlikView by using hierarchies (please see QV Reference Manual, page 489; NodeId=PRODUCTID, PARENTID=JOINTPRODUCT), in order to get described result.

regards,

Kruno

Subselect in sql script

Anonymous — Wed, 18 Jan 2012 19:07:28 GMT

Hi Krunoslav,

Thanks for your reaction. I have read the Reference Manual, but can't translate it to my specific issue.

Don't you think it can be solved with a subselect / subquery?

Kind regards,

Henco

Re: Subselect in sql script

nstefaniuk — Thu, 19 Jan 2012 08:24:12 GMT

Hi. You have modified the query no ? This query can't retrieve the results you show us, because of a inner join between product and storehouse and AD121 have no product.id. Here is the SQL that can retrieve the results:

WITH PRODUCT AS
(SELECT 'AD120' "PRODUCTID",
NULL "JOINTPRODUCT",
1 "ID" FROM DUAL
UNION
SELECT 'AD121' "PRODUCTID",
'AD120' "JOINTPRODUCT",
NULL "ID" FROM DUAL),
STOREHOUSE AS
(SELECT 1 "PRODUCTID",
10 "STOREHOUSEID" FROM DUAL)
-- Start of query
SELECT
     PRODUCT.PRODUCTID,
     PRODUCT.JOINTPRODUCT,
     STOREHOUSE.STOREHOUSEID
FROM
     PRODUCT,
     STOREHOUSE
WHERE
     PRODUCT.ID = STOREHOUSE.PRODUCTID(+);

And here is the query that solves your issue (query starts at "Start of query", the WITH clauses are here to give me virtual tables).

What the query does? It pre-calculate a virtual table product by seeking the product.id from parent if there is no product.id in the row. So for AD121, as there is no product.id, the sub-query will seek the product.id of AD121's parent : AD120.


WITH PRODUCT AS
(SELECT 'AD120' "PRODUCTID",
NULL "JOINTPRODUCT",
1 "ID" FROM DUAL
UNION
SELECT 'AD121' "PRODUCTID",
'AD120' "JOINTPRODUCT",
NULL "ID" FROM DUAL),
STOREHOUSE AS
(SELECT 1 "PRODUCTID",
10 "STOREHOUSEID" FROM DUAL)
-- Start of query
SELECT
     "PRODUCT_UNIFIED".PRODUCTID,
     "PRODUCT_UNIFIED".JOINTPRODUCT,
     STOREHOUSE.STOREHOUSEID
FROM
     (Select
        "PRODUCT".PRODUCTID "PRODUCTID",
        "PRODUCT".JOINTPRODUCT "JOINTPRODUCT",
        nvl("PRODUCT".ID, "PARENT_PRODUCT".ID) "ID"
      from
        PRODUCT "PRODUCT"
        left join PRODUCT "PARENT_PRODUCT" on ("PARENT_PRODUCT".PRODUCTID = "PRODUCT".JOINTPRODUCT)
     ) "PRODUCT_UNIFIED",
     STOREHOUSE
WHERE
     "PRODUCT_UNIFIED".ID = STOREHOUSE.PRODUCTID(+);

Re: Subselect in sql script

qliksus — Thu, 19 Jan 2012 09:32:20 GMT

Assuming you are writing this query in the edit script you can try like this

FinalTable1 :

select

productid,
jointproduct,
storehouseid

from product,
storehouse
where product.id = storehouse.productid;

join

select

productid as jointproduct,
storehouseid as storehouseidfornull

from product,
storehouse
where product.id = storehouse.productid;

FinalTable:

load

productid,
jointproduct,
if( isnull(storehouseid) ,storehouseidfornull ,storehouseid) as storehouseid

load

productid,
jointproduct,
storehouseid,

storehouseidfornull

resident FinalTable1 ;

drop table FinalTable1;

Re: Subselect in sql script

Thu, 19 Jan 2012 11:38:50 GMT

Hi,

here is example how it can be done with hierarchy (just copy code below and reload script):

TABLE:

LOAD * INLINE [

PRODUCTID, JOINTPRODUCT, STOREHOUSEID

AD120, , 10

AD121, AD120

];

TABLE1:

Hierarchy (PRODUCTID, JOINTPRODUCT, STOREHOUSEID) LOAD Distinct PRODUCTID, JOINTPRODUCT, STOREHOUSEID

Resident TABLE;

drop Field STOREHOUSEID, STOREHOUSEID2;

RENAME Field STOREHOUSEID1 to STOREHOUSEID;

DROP Table TABLE;

Re: Subselect in sql script

flipside — Thu, 19 Jan 2012 12:02:32 GMT

Hi,

As ever with Qlikview, there's always a number of ways to do things, here's another ...

SourceData:
LOAD * INLINE [
PRODUCTID, JOINTPRODUCT, STOREHOUSEID
AD120,,10
AD121,AD120,];

Products:
LOAD
PRODUCTID,
if(LEN(LTRIM(STOREHOUSEID))=0,Lookup('STOREHOUSEID','PRODUCTID',JOINTPRODUCT,'SourceData'),STOREHOUSEID) as STOREHOUSEID
resident SourceData;

drop table SourceData;

flipside

Subselect in sql script

Anonymous — Thu, 19 Jan 2012 12:07:23 GMT

Hi everyone,

Thanks a lot for your help, I'm impressed

Tonight or tomorrow I have time to find out which solution works for me. I will definately let you know what my findings are, and of course check your answers as helpful / correct.

Thanks again.

Kind regards,

Henco