topic Create Set Analysis from SQL Statement in QlikView

Create Set Analysis from SQL Statement

Tue, 15 Jul 2014 12:30:39 GMT

Hi everybody,

Please what is the equivalent of this SQL Statement in SET Analysis ?

SELECT Aux.DRADV, SUM(Aux.nbre_de_lignes)

FROM (

SELECT DISTINCT dr.Date_de_reception_ADV as DRADV, dd.*

FROM adv.dataadvagent dr, adv.dataadvagent dd

WHERE dr.Date_de_reception_ADV < dd.date_de_traitement

AND dd.Date_de_reception_ADV <= dr.Date_de_reception_ADV

AND dr.Date_de_reception_ADV IS NOT NULL AND dr.date_de_traitement IS NOT NULL AND dd.Date_de_reception_ADV IS NOT NULL AND dd.date_de_traitement IS NOT NULL

) Aux

GROUP BY Aux.DRADV

LIMIT 2000

Thanks in advance.

Re: Create Set Analysis from SQL Statement

Gysbert_Wassenaar — Tue, 15 Jul 2014 12:37:52 GMT

There is no equivalent. Set analysis is used in chart expressions. It cannot be used in the script.

Re: Create Set Analysis from SQL Statement

Tue, 15 Jul 2014 14:38:21 GMT

So, It's not possible to write a SET Analysis that will give me the same result ?

Re: Create Set Analysis from SQL Statement

Gysbert_Wassenaar — Tue, 15 Jul 2014 14:48:54 GMT

No, that's not possible. You will have to use a chart object and add a dimension and an expression. The expression can be a set analysis expression that can filter the set of records using some of the criteria your sql statement also contains. But a set analysis expression cannot generate a multi-column record set.

Re: Create Set Analysis from SQL Statement

Tue, 15 Jul 2014 15:23:48 GMT

It's exactly what I would like to do. Create a chart that display the KPI obtains from the statement by date.

But at the Expression page I don't know what to write to get the KPI.

Re: Create Set Analysis from SQL Statement

Gysbert_Wassenaar — Wed, 16 Jul 2014 13:51:32 GMT

First you will need to load the data. You can use your original sql statement or load the unaggregated data using only the sub-select statement:

MyData:

SQL SELECT DISTINCT dr.Date_de_reception_ADV as DRADV, dd.*

FROM adv.dataadvagent dr, adv.dataadvagent dd

WHERE dr.Date_de_reception_ADV < dd.date_de_traitement

AND dd.Date_de_reception_ADV <= dr.Date_de_reception_ADV

AND dr.Date_de_reception_ADV IS NOT NULL AND dr.date_de_traitement IS NOT NULL AND dd.Date_de_reception_ADV IS NOT NULL AND dd.date_de_traitement IS NOT NULL

FROM ...mysourcetable...;

Once you've load the data you can create for example a straight table object with the field DRADV as dimension and as expression sum([dd.nbre_de_lignes]). I'm not sure of the field names in your document. I'm just guessing based on the sql statement you posted. Make sure to use the exact case-sensitive field names. The expression editor allows you to select the field names and paste them into the expression. That way you can be sure you get it right.