topic Re: Set Analysis SubQuery in QlikView

Set Analysis SubQuery

Fri, 12 Jun 2015 15:21:55 GMT

I have a disadvantage developing the following scrip Set Analysis.

I have 2 subquery to obtain the same result

The 1st form:

SELECT SUM(A.SALDO-B.SALDO)

FROM (

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201502' AND CREESTID='02'

GROUP BY ASOID

) A

INNER JOIN(

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201503' AND CREESTID='02'

GROUP BY ASOID

) B

ON A.ASOID=B.ASOID

___________________________________________

The 2nd form:

SELECT SUM(SALDO) FROM

(SELECT A.ASOID,SUM(B.SALDO-A.SALDO) SALDO

FROM

(SELECT ASOID, SUM(SALDO_CAPITAL ) SALDO

FROM CRE_INVENTARIO

WHERE CREESTID='02' AND PERIODO='201503'

GROUP BY ASOID) A

INNER JOIN

(SELECT ASOID, SUM(SALDO_CAPITAL ) SALDO

FROM CRE_INVENTARIO

WHERE CREESTID='02' AND PERIODO='201502'

GROUP BY ASOID) B

ON A.ASOID=B.ASOID

GROUP BY A.ASOID

)

_________________________

CAN SOMEONE SOLVE THIS CASE IN SET ANALYSIS??

Thanks for helping!

Regards

Chris

Re: Set Analysis SubQuery

ramoncova06 — Fri, 12 Jun 2015 20:39:27 GMT

yes this can be done in set analysis, assuming your fields have the same name

sum({<CREESTID={'02'},PERIODO={'201503'}>}SALDO)-sum({<CREESTID={'02'},PERIODO={'201502'}>}SALDO)

here is a little more on set analysis

A Primer on Set Analysis

Set Analysis: syntaxes, examples

Re: Set Analysis SubQuery

Sat, 13 Jun 2015 04:57:44 GMT

I fear that the result is not as expected.

I also consider equally, but this analysis does not consider there to join ASOID for both PERIODO (201 503) AND PERIODO (201502)

Re: Set Analysis SubQuery

Sat, 13 Jun 2015 08:58:57 GMT

please try this expression:

sum({<

ASOID = p({< period = {'201502'}, CREESTID= {'02'}>}ASOID) * p({< period = {'201503'}, CREESTID= {'02'}>}ASOID),

period = {'201502'},

CREESTID= {'02'} >}SALDO_CAPITAL

)

sum({<

ASOID = p({< period = {'201502'}, CREESTID= {'02'}>}ASOID) * p({< period = {'201503'}, CREESTID= {'02'}>}ASOID),

period = {'201503'},

CREESTID= {'02'} >}SALDO_CAPITAL

)

Re: Set Analysis SubQuery

ramoncova06 — Mon, 15 Jun 2015 14:11:46 GMT

is you join internal in qlikview also ?

can you share the model ?

sql syntax is similar to QV backend script, but not to QV set analysis, so with the assumption that your inventory table is the fact, my syntax should work

Re: Set Analysis SubQuery

Tue, 16 Jun 2015 20:19:43 GMT

Great looks good shared syntax, however after its implementation does not generate the same result as the SQL query.

I was reviewing the results and the difference is that the result of the expression in analysis generates the result set

SELECT SUM (A.SALDO) - SUM (B.SALDO)

FROM (

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201502' AND CREESTID='02'

GROUP BY ASOID

) A

INNER JOIN(

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201503' AND CREESTID='02'

GROUP BY ASOID

) B

ON A.ASOID=B.ASOID

While the expectation is that the syntax dispose of the result set

SELECT SUM (A.SALDO-B.SALDO)

FROM (

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201502' AND CREESTID='02'

GROUP BY ASOID

) A

INNER JOIN(

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201503' AND CREESTID='02'

GROUP BY ASOID

) B

ON A.ASOID=B.ASOID

Re: Set Analysis SubQuery

Wed, 24 Jun 2015 20:30:08 GMT

Great looks good shared syntax, however after its implementation does not generate the same result as the SQL query.

I was reviewing the results and the difference is that the result of the expression in analysis generates the result set

SELECT SUM (A.SALDO) - SUM (B.SALDO)

FROM (

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201502' AND CREESTID='02'

GROUP BY ASOID

) A

INNER JOIN(

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201503' AND CREESTID='02'

GROUP BY ASOID

) B

ON A.ASOID=B.ASOID

While the expectation is that the syntax dispose of the result set

SELECT SUM (A.SALDO-B.SALDO)

FROM (

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201502' AND CREESTID='02'

GROUP BY ASOID

) A

INNER JOIN(

SELECT ASOID,SUM(SALDO_CAPITAL) SALDO FROM CREDITOS_CAB

WHERE PERIODO='201503' AND CREESTID='02'

GROUP BY ASOID

) B

ON A.ASOID=B.ASOID