topic Re: Why do I get an invalid expression error when trying to use sum and group by? in QlikView

Why do I get an invalid expression error when trying to use sum and group by?

Fri, 18 Jul 2014 12:55:13 GMT

I have the following as my load script.

LOAD text(inventory_num) as inv_num, sum(amt_paid) as amt_paid_2014_06 FROM C:\Downloads\invpaid.txt (txt, codepage is 1252, embedded labels, delimiter is '~') GROUP by inv_num;

The invpaid.txt file has inventory numbers and the amount paid for each number. Sometimes there are multiple payments per inventory number. I want to consolidate the data so that I have only unique inventory numbers with the total amount paid for each number.

For example

inv_num	amt_paid
a1	30
a1	31
a2	30

The above table should turn into

inv_num	amt_paid
a1	61
a2	30

Re: Why do I get an invalid expression error when trying to use sum and group by?

alexandros17 — Fri, 18 Jul 2014 13:02:14 GMT

Try this:

LOAD text(inventory_num) as inv_num, sum(amt_paid) as amt_paid_2014_06 FROM C:\Downloads\invpaid.txt (txt, codepage is 1252, embedded labels, delimiter is '~') GROUP by text(inventory_num);

you cannot use alias ...

Re: Why do I get an invalid expression error when trying to use sum and group by?

Anonymous — Fri, 18 Jul 2014 13:03:03 GMT

Try this :

LOAD text(inventory_num) as inv_num, sum(amt_paid) as amt_paid_2014_06 FROM C:\Downloads\invpaid.txt (txt, codepage is 1252, embedded labels, delimiter is '~') GROUP by inventory_num ;

Re: Why do I get an invalid expression error when trying to use sum and group by?

simenkg — Fri, 18 Jul 2014 13:04:51 GMT

You are grouping by a field that has not yet been defined.

try:

LOAD text(inventory_num) as inv_num, sum(amt_paid) as amt_paid_2014_06 FROM C:\Downloads\invpaid.txt (txt, codepage is 1252, embedded labels, delimiter is '~') GROUP by inventory_num;

Regards
SKG

Re: Why do I get an invalid expression error when trying to use sum and group by?

Fri, 18 Jul 2014 13:06:46 GMT

Thanks Bill! Your solution worked.