SQl Select and Load help with Aggregation

[dthornburg]

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";

[Gysbert_Wassenaar]

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.

[dthornburg]

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?

[Gysbert_Wassenaar]

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";

[dthornburg]

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?

[Anonymous]

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

[#7 · Accepted solution]

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";

[dthornburg]

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;

[dthornburg]

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