Set Analysis SubQuery

[#1]

Hi

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

[ramoncova06]

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

[#3]

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)

[#4]

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

)

[ramoncova06]

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

[#6 · Accepted solution]

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

[#7]

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