aggregation fonction in script

[#1]

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.

[Gysbert_Wassenaar]

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.

[#3]

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?

[Gysbert_Wassenaar]

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 ... ;

[#5]

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

[Gysbert_Wassenaar]

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.

[#7]

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.

[#8]

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

[Gysbert_Wassenaar]

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