topic Set Analysis Query in QlikView

Set Analysis Query

Mon, 07 Sep 2009 23:29:53 GMT

Hi There,

I'm wondering if anyone is able to assiste with the following query I need to run through set analysis

SELECT SUM(mems) FROM
(
( SELECT count(*) AS mems
FROM table1
WHERE date_range BETWEEN min(report_date) and max(report_date)
AND status = 'C'
AND (channel <> old_channel
OR package <> old_package_id)
GROUP BY package, channel, pmi
HAVING package <> 14
)
)

Thanks,

Kevin.

Set Analysis Query

prieper — Tue, 08 Sep 2009 03:21:47 GMT

Hi,

not quite clear to me, what your intention is and which is the relation to set-analysis.

Peter

Set Analysis Query

johnw — Wed, 09 Sep 2009 01:47:50 GMT

Almost impossible for me to get this right on the first try without an example QVW to test it on and some more information, such as if old_channel and old_package_id are single values or a list of values, but something like this?

sum(aggr(count({<date_range={">=$(=date(min(report_date))) <=$(=date(max(report_date)))"}
,status={C}
,package-={14}
>}
if(channel <> old_channel
or package <> old_package_id,1))
,package,channel,pmi))

I implemented your OR without using set analysis. You CAN do a union of two sets, but I frankly expect that the performance would be worse than just doing the OR separately.

By the way, if I understand your SQL, the "having package <> 14" isn't any different than just putting that in as another condition in the select. I believe having is normally used when you need to check the RESULT of the SQL, such as if you wanted "having mems > 20".