Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Group by function in script

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 !1.PNG2.PNG

1 Solution

Accepted Solutions

Re: Group by function in script

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

13 Replies

Re: Group by function in script

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
Esteemed Contributor

Re: Group by function in script

In the second LOAD, you need to change:

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

Andy

jsanchezh
Contributor

Re: Group by function in script

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

Not applicable

Re: Group by function in script

Thank you,

It seems that it works.

Best regards,

Yasser

Not applicable

Re: Group by function in script

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
Contributor

Re: Group by function in 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

Not applicable

Re: Group by function in script

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
Contributor

Re: Group by function in script

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,

Not applicable

Re: Group by function in script

It does, really thank you.

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

Capture.PNG

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.

Community Browser