Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
There is no equivalent. Set analysis is used in chart expressions. It cannot be used in the script.
So, It's not possible to write a SET Analysis that will give me the same result ?
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.
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.
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.