topic Re: SQl Select and Load help with Aggregation in QlikView

SQl Select and Load help with Aggregation

dthornburg — Wed, 15 May 2013 20:18:18 GMT

Can someone tell me how I can correct the below load statement. The error I get is incoice_line_misc_qty is not found.

LOAD "invoice_nbr",

"line_nbr",

"charge_id" AS invoice_line_misc_charge_id,

invoice_line_misc_qty,

invoice_line_misc_amount;

SQL SELECT

invoice_nbr,

line_nbr,

charge_id,

SUM(qty_invoiced) AS invoice_line_misc_qty,

SUM(extension) AS invoice_line_misc_amount

FROM PUB."ivc_mchgs"

GROUP BY

"invoice_nbr",

"line_nbr",

"charge_id";

Re: SQl Select and Load help with Aggregation

Gysbert_Wassenaar — Thu, 16 May 2013 06:21:27 GMT

Check for typos. incoice_line_misc_qty: incoice or invoice?

Qlikview is case sensitive so check that your using the same case senstive field names.

And try removing the preceding load to check that the sql select actually returns records.

Re: SQl Select and Load help with Aggregation

dthornburg — Thu, 16 May 2013 11:48:25 GMT

No typos in script. There was one in my original post. Sorry about that. It is possible that there are no records because this is a misc. invoice table but it should still process. Any other ideas?

The SQL select works fine without the load statement. The issue I am hving is I need invoice_line_misc_qty later in the script for calculations. How can I load the summed up quantities into a load statement?

Re: SQl Select and Load help with Aggregation

Gysbert_Wassenaar — Thu, 16 May 2013 12:38:53 GMT

I don't know why the preceding load doesn't work. It looks correct to me. But you shouldn't need the preceding load if all you want to do is rename fields. The original field name is used in the group by afaik.

SQL SELECT

invoice_nbr,

line_nbr,

charge_id AS invoice_line_misc_charge_id,

SUM(qty_invoiced) AS invoice_line_misc_qty,

SUM(extension) AS invoice_line_misc_amount

FROM PUB."ivc_mchgs"

GROUP BY

"invoice_nbr",

"line_nbr",

"charge_id";

Re: SQl Select and Load help with Aggregation

dthornburg — Thu, 16 May 2013 12:56:37 GMT

I removed the charge_id. Do not really need it.

The current load statement works but is not what I need.

LOAD "invoice_nbr",

"line_nbr",

"qty_invoiced" AS invoice_line_misc_qty,

"extension" AS invoice_line_misc_amount;

SQL SELECT

invoice_nbr,

line_nbr,

qty_invoiced,

extension

FROM PUB."ivc_mchgs";

When I add the sum function which is what I am after I get the following error:

Field not found invoice_line_misc_qty.

Script that does not work:

LOAD "invoice_nbr",

"line_nbr",

invoice_line_misc_qty,

invoice_line_misc_amount;

SQL SELECT

invoice_nbr,

line_nbr,

SUM(qty_invoiced) AS invoice_line_misc_qty,

SUM(extension) AS invoice_line_misc_amount

FROM PUB."ivc_mchgs"

GROUP BY

invoice_nbr,

line_nbr;

qty_invoiced,

extension;

If I comment out the load statement it works but I cannot use the summed up values later in my script. Any ideas?

Re: SQl Select and Load help with Aggregation

Anonymous — Thu, 16 May 2013 13:06:04 GMT

If you remove preceding load, what is the field name? Is it invoice_line_misc_qty, or maybe INVOICE_LINE_MISC_QTY?

Re: SQl Select and Load help with Aggregation

Thu, 16 May 2013 13:13:56 GMT

try this

LOAD

invoice_nbr,

line_nbr,

sum(qty_invoiced) AS invoice_line_misc_qty,

sum(extension) AS invoice_line_misc_amount

GROUP BY invoice_nbr, line_nbr;

SQL SELECT

invoice_nbr,

line_nbr,

qty_invoiced,

extension

FROM PUB."ivc_mchgs";

Re: SQl Select and Load help with Aggregation

dthornburg — Thu, 16 May 2013 13:14:44 GMT

The below works but I need the summed up later in a LOAD statment to accumulate the results.

SQL SELECT

invoice_nbr,

line_nbr,

SUM(qty_invoiced) AS invoice_line_misc_qty,

SUM(extension) AS invoice_line_misc_amount

FROM PUB."ivc_mchgs"

GROUP BY

invoice_nbr,

line_nbr;

Later in script I summ up some values and I receive an error.

Field not found invoice_line_misc_amount.

Load *,

IF(invoice_line_misc_amount>0,IF(invoice_type='5602',(inv_line_amount + invoice_line_misc_amount)*-1,(inv_line_amount + invoice_line_misc_amount)*1),

IF(inv_line_amount>0,IF(invoice_type='5602',(inv_line_amount)*-1,(inv_line_amount)*1),

IF(invoice_type='5602',(invoice_amt + misc_chg_amt)*-1,(invoice_amt + misc_chg_amt)*1))) AS invoice_dol,

IF(invoice_line_misc_amount>0,IF(invoice_type='5602',(extension + invoice_line_misc_amount)*-1,(extension + invoice_line_misc_amount)*1),

IF(extension>0,IF(invoice_type='5602',(extension)*-1,(extension)*1),

IF(invoice_type='5602',(invoice_amt + misc_chg_amt)*-1,(invoice_amt + misc_chg_amt)*1))) AS invoice_line_extension,

IF(invoice_type='5602',line_lbs_invoiced*-1,line_lbs_invoiced*1) AS lbs_invoiced,

IF(invoice_type='5602',invoice_cost_db*-1,invoice_cost_db*1) AS invoice_cost

Resident ivc_header;

drop table ivc_header;

Re: SQl Select and Load help with Aggregation

dthornburg — Thu, 16 May 2013 13:22:40 GMT

That worked great. Thank you. Out of curosity; Which is the more efficent? Having the group by in load statement or SQL statement?