Group by function in script

[#1]

Good morning,

I tried a simple script to get used to group by function, however, the script didn't run. Here is a screen shot of the script and the error message.
Can anyone tell me what is wrong.

Thank you all !

[ashfaq_haseeb · Accepted solution]

Hi,

Try like below

Temp:

Load

Achat,

Produit,

prix,

debut,

fin

from C:\....;

Load

Achat,

avg(prix) as Avgprix

Resident Temp

Group By Achat;

Regards

ASHFAQ

[awhitfield]

In the second LOAD, you need to change:

from avg(prix) to avg(prix) as prix

Andy

[jsanchezh]

When you use a Group by funcion on the script, you have to "group by" every field but the one with the agregation function. That's why ASHFAQ creates a second table where he only groups by Achat.

Regards

[#5]

Thank you,

It seems that it works.

Best regards,

Yasser

[#6]

Alright, so every field in a load with group by needs to be associatate to an agregation function? Is that correct.
Also how to add numerous columns in the script?

[jsanchezh]

For example in your first table if you want to add a column where you want to write the word 'sales'in a field called transaction_type, you do someting like this:

Temp:

Load

Achat,

Produit,

prix,

debut,

fin,

'sales' as transaction_type

from C:\....;

so you can add columns with new expressions giving them names

Regards

[#8]

That is clearer. And is it possible to add a column with variables and not text. For example, feel the new columns with values of a row in the datasource?

[jsanchezh]

Yes, you can use variables and field names from your data source. Imagine you want a new column which have a string chain that has your field produit and prix

you can add to your load statement:

     Produit&' - '&prix as produit_prix

Then, if you have a produit called champoo with a prix 0.99, your chain will look like champoo - 0.99

Hope it helps,

[#10]

It does, really thank you.

actually what I am trying to do is the the explained in the picture:

I have been able to do so with pivot table. How ever, because I need to draw charts with the tables having only one line per purchase ID, I don't know how to figure it out.

Best regards,

Yasser.