Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create Set Analysis from SQL Statement

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.

5 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand