topic Script Help Joining 3 Tables in QlikView

Script Help Joining 3 Tables

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

I have 3 tables. Invoice, Invoice Line, and Invoice misc charges. I want to capture all the charges. The issue I am having is the values are in the invoice table dollar values are in every table. The key is the invoice number, that gets me the invoice lines, invoice misc charges key is the invoice number, invoice line and misc charge seq. I currently am doing a join but if there are 2 misc charges for 1 line I get a duplicate record and the invoice line total is doubled. My goal is to add the misc. charges in the invoice misc charges table to the invoice line table. How can this be accomplished in the script?

Invoice Table

Invoice number 1 misc charge of $5

Invoice Line Table

invoice 1 item nbr 1 charge $3

invoice 1 item nbr 2 charge $5

Invoice misc charges table

invoice number 1 item nbr 1 misc seq 1 charge $10

invoice number 1 item nbr 1 misc seq 2 charge $5

Desired Result

Invoice 1 charge = $23

Invoice 2 charge = $10

Current result is

Invoice 1 charge = $18

Invoice 1 charge = $13

Invoice 2 charge = $10

Re: Script Help Joining 3 Tables

jpapador — Wed, 15 May 2013 18:22:08 GMT

Try this, join all three tables. Then do another left join with the following table,

Left Join

LOAD

Invoice Number,

Sum(Charge1) + Sum(Charge2) + Sum(Charge3) as Total Charges

Resident (Original Table Here)

Group by Invoice Number;

Re: Script Help Joining 3 Tables

dthornburg — Wed, 15 May 2013 18:42:11 GMT

I tried adding the group by to current load statement. I am now getting an aggregation expression required by GROUP by clause. Any ideas?

Load statement below:

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

Group by invoice_nbr;

drop table ivc_header;

Re: Script Help Joining 3 Tables

dthornburg — Wed, 15 May 2013 18:51:04 GMT

I see my issue. I am trying solution you provided now.