topic Re: aggregation fonction in script in QlikView

aggregation fonction in script

Thu, 06 Aug 2015 13:41:20 GMT

Hello People,

I have a trouble with aggregation in script,

My scriot is :

The field [Identifiant] is just a ID
[Pose / Dépose] can only take to string value : 'Pose' and 'Dépose' ;

[ETI] can take lot value pas what's important for me is also two values lets say 'ar' and 'st'

[Nature] can take 21 values lets say Nature_1, Nature_2... Nature_21; but in reallity i

[Qté prévue] is quantiy is a numeric value
[Année de construction] containt year but its not important for me yet.

What i want to do is sum([Qté prévue]) but with some conditions :

First i'm not interressed by sum([Qté prévue]) for each Nature( 1, 2...21) separatly , but im interressed in :

sum([Qté prévue]) for a groupe of [Nature] together

For exemple a field called [Qté Prevue (Nature-1-2-3)] that containt sum([Qté prévue]) where [Nature]= {Nature_1, Nature_2, Nature_3}.

another field called [Qté Prevue (Nature-5-6-8)] that containt sum([Qté prévue]) where [Nature]= {Nature_5, Nature_6, Nature_8}.

And i want lets say [Qté Prevue (Nature-1-2-3) & Pose] where [Pose / Dépose]= 'Pose'
and [Qté Prevue (Nature-1-2-3) & Dépose] where [Pose / Dépose]= 'Dépose' . i want that also for [Qté Prevue (Nature-5-6-8) Pose] and [Qté Prevue (Nature-5-6-8) Dépose]....

Once have that i want [Qté Prevue (Nature-1-2-3) & Pose & ar] where [ETI]= 'ar' aand [Qté Prevue (Nature-1-2-3) & Pose & st] where [ETI]= 'st'.

I would like to do in a script for performance purpose.

Thanks and ask me if you didnt understant well.

Thanks you.

Re: aggregation fonction in script

Gysbert_Wassenaar — Thu, 06 Aug 2015 13:48:53 GMT

I don't quite understand what you're trying to do but you can use aggregation functions like sum in the script. Something like

Load

sum([Qté prévue]) as Sum

Nature

From ....

Group By Nature;

Any field not used in an aggregation function must be listed in the Group By clause.

I hope this gives you enough information to work out a solution.

Re: aggregation fonction in script

Thu, 06 Aug 2015 14:07:47 GMT

thanks for the help,

What i want is some new fields with the sum([Qté prévue]) where Match(Nature, 'XX', 'YY'...)

again for all Nature values.

You understand?

Re: aggregation fonction in script

Gysbert_Wassenaar — Thu, 06 Aug 2015 14:15:04 GMT

Then first create a field with the Nature groups and then create the sums using only that new field in the group by clause.

LOAD sum([Qté prévue]) as Sum, Nature_Groups

Group By Nature_Groups;

LOAD

if(match(Nature, 'a','b','c'), 'Nature_abc',

if(match(Nature, 'd','e','f'), 'Nature_def',

if(....etc )

)

) as Nature_Groups,

[Qté prévue]

FROM ... ;

Re: aggregation fonction in script

Thu, 06 Aug 2015 14:42:55 GMT

thanks, i'll try and come back to you, but i have a question : its not a problem to use Nature_Groups in clause before created it like you do in your example syntax??

Thanks

Re: aggregation fonction in script

Gysbert_Wassenaar — Thu, 06 Aug 2015 14:48:34 GMT

That's not what happens. First the groups are created and then the sums are calculated in a preceding load. The data from the second LOAD is piped to the preceding LOAD directly above it.

Re: aggregation fonction in script

Thu, 06 Aug 2015 15:27:32 GMT

Hi Gysbert i tried your syntahx but i get an error

here is my script :

LOAD

Identifiant,

[Pose / Dépose],

ETI,

[Nature],

sum([Qté prévue]) as Sum,

Nature_Groups

Group By Nature_Groups,Identifiant_Moap;//[Pose / Dépose], ETI_MoaPilot;

LOAD

If(Match([Nature],'35','50','37,7'), 'Al_1',

If(Match([Nature],'59,7'), 'AL_2',

If(WildMatch([Nature],'75,5','95','75,6'), 'FS_1',

If(wildMatch([Nature],'148','228','150','240','147,1'), 'FS_2')))) as Nature_Groups,

Identifiant,

[Pose / Dépose],

ETI,

[Nature],

[Qté prévue]

FROM ....

Ioad everything because, i want also sum([Qté prévue]) where [Pose / Dépose] ='Pose' and where [Pose / Dépose] ='Dépose' separatly, and i want that too for all identifiant

you get it?

Thanks.

Re: aggregation fonction in script

Sun, 09 Aug 2015 18:52:03 GMT

Hi Wassenaar,

here is another eplication of my problem :

i have i a problem with a table, here is the table following by what i want :

Current Table (iQlikview table that i export to excel) :

What i want :

i want two table or one big table (i slipt it for presentation purpose).

My solution was to do a generic load, but in my case i have more that one attribuate field ( [Pose /Dépose], ETI and Nature,)

Someone know how to to that?

Thanks

Re: aggregation fonction in script

Gysbert_Wassenaar — Mon, 10 Aug 2015 07:58:25 GMT

Any field not used in an aggregation function must be listed in the Group By clause.