topic Re: Expressions with group by into script in App Development

Expressions with group by into script

creepingdeath — Wed, 22 Dec 2021 15:42:54 GMT

Hello,

I want to calculate multiple expressions into one load script but when I run this code I get wrong and incoherent data.

Is this the correct syntax?

Total_par_activité:
Load
count(Ordre) as TOTAL_JALONS_PAR_ACTIVITE,Code_Activite,Date_Circulation
Resident TablePrincipale
group by Date_Circulation,Numero_Sillon,CR_CI_CH,Type_Horaire,Code_Activite;

Taux_de_couverture_par_technique_detection:
LOAD
count(Ordre) as nombre_de_jalons_couverts_par_technique_detection,Date_Circulation,Technique_Detection
RESIDENT TablePrincipale
where Jalon_Couvert=1
group by Date_Circulation,Numero_Sillon,CR_CI_CH,Type_Horaire,Technique_Detection;

Taux_de_couverture_par_technique_detection_et_source:
LOAD
count(Ordre) as nombre_de_jalons_couverts_par_technique_detection_et_source,Date_Circulation,Technique_Detection,Source
RESIDENT TablePrincipale
where Jalon_Couvert=1
group by Date_Circulation,Numero_Sillon,CR_CI_CH,Type_Horaire,Technique_Detection,Source;

Re: Expressions with group by into script

GaryGiles — Sun, 25 Apr 2021 11:15:28 GMT

Since your Group By clauses contain fields that are not included in your Load clauses, you are aggregrating the data a more granular level than the data that is loaded into each table. This will cause you to have multiple rows for each combination of Dimension values in each table. Generally, you will want the fields that are in the Group By clause to be included in the load statement for the table.

Not sure what the requirements are, but I would start by evaluating whether you need to add fields to the Load clause or removed them from the Group By clause.

BTW, you could accomplish any of these aggregation levels via set analysis in chart objects instead of loading them them into pre-aggregated tables.

Re: Expressions with group by into script

creepingdeath — Mon, 26 Apr 2021 09:07:04 GMT

Thank you for your answer, I have a qvd data file in which my primary key is composed by the following fields " Date_Circulation,Numero_Sillon,CR_CI_CH,Type_Horaire" I want to calculate many charts with differnet expressions and different group by clauses.

All these expressions were on my charts before but I moved them to the script for performance concerns (when loading 3 month of data Qlikview crashes).

I tested as you mentioned above but it didn't work, Seems that Qlikview was only loading the data from the last load

Is there any other way to do this ?

Re: Expressions with group by into script

creepingdeath — Mon, 26 Apr 2021 13:30:35 GMT

This is the result when I use Set Analysis instead of load script, It crashes every time I want to change something

And this is what it looks like when I load one month of data

NB: I don't have a Qlikview server I work only on my laptop

Any help please ?