topic Re: Group by function in script in QlikView

Group by function in script

Wed, 22 Apr 2015 09:02:15 GMT

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 !

Re: Group by function in script

ashfaq_haseeb — Wed, 22 Apr 2015 09:07:44 GMT

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

Re: Group by function in script

awhitfield — Wed, 22 Apr 2015 09:16:01 GMT

In the second LOAD, you need to change:

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

Andy

Re: Group by function in script

jsanchezh — Wed, 22 Apr 2015 09:16:26 GMT

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

Re: Group by function in script

Wed, 22 Apr 2015 09:16:31 GMT

Thank you,

It seems that it works.

Best regards,

Yasser

Re: Group by function in script

Wed, 22 Apr 2015 09:22:20 GMT

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?

Re: Group by function in script

jsanchezh — Wed, 22 Apr 2015 09:32:33 GMT

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

Re: Group by function in script

Wed, 22 Apr 2015 09:34:17 GMT

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?

Re: Group by function in script

jsanchezh — Wed, 22 Apr 2015 09:49:02 GMT

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,

Re: Group by function in script

Wed, 22 Apr 2015 09:54:13 GMT

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.

Re: Group by function in script

jsanchezh — Wed, 22 Apr 2015 11:58:19 GMT

I did this, but it works if you know a maximum number of products on each purchase

Re: Group by function in script

Wed, 22 Apr 2015 12:17:34 GMT

Thank you a lot

Re: Group by function in script

jsanchezh — Thu, 23 Apr 2015 07:35:03 GMT

you can mark some of my answers as "useful" if you wish

Re: Group by function in script

pratap6699 — Thu, 23 Apr 2015 09:41:29 GMT

Temp:

Load

Achat,

Produit,

prix,

debut,

fin

from C:\....;

Load

Achat,

Produit,

prix,

debut,

fin

avg(prix) as Avgprix

Resident Temp

Group By Achat,prodit,prix,debut,fin;