Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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
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
In the second LOAD, you need to change:
from avg(prix) to avg(prix) as prix
Andy
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
Thank you,
It seems that it works.
Best regards,
Yasser
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?
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
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?
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,
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.