topic Re: Data Load Sql in QlikView

Data Load Sql

Anonymous — Mon, 05 Oct 2015 06:12:49 GMT

Hi,

I'm trying to take data from 2 different tables but I couldn't succeeded. Here is my code,

VOUCHER:

LOAD *

;

select bi.id,o.total_voucher, bi.persisted_price * bi.quantity,

round((o.total_voucher * bi.persisted_price * bi.quantity) / total , 2)

from order_order o, order_basketitem bi, (

select oo.id as o_id, sum(bii.persisted_price * bii.quantity) as total

from order_order oo, order_basketitem bii

where oo.id = bii.order_id

and oo.persisted_tracker_code = '127707332191417'

group by 1

) as xx

where o.id = bi.order_id

and o.persisted_tracker_code = '127707332191417'

and xx.o_id = o.id

this one works, but when I try to remove <and o.persisted_tracker_code = '127707332191417'> filter, i get an error like <division by zero>

and also when it works, this table doesn't set up any relation with other tables.

How can I fixed these issues? Can you help me please.

Regards,

Berker.

Re: Data Load Sql

petter — Mon, 05 Oct 2015 12:35:54 GMT

The reason you get a divison by zero is in your SQL code as you calculate a total with this:

sum(bii.persisted_price * bii.quantity) as total

and then further up in the SQL this calculated column is used in a division:

(o.total_voucher * bi.persisted_price * bi.quantity) / total

So if the first sum will be 0 for any id you will get this error. And by "luck" the persisted_tracker_code = '127707332191417' does not return a 0 and for that reason calculates without an error.

You have to modify your SQL to cater for the fact that you might get a total of zero - maybe return a 0 or null like this:

case total when 0 then 0 else (o.total_voucher * bi.persisted_price * bi.quantity) / total end

So your SQL could look like this:

select bi.id,o.total_voucher, bi.persisted_price * bi.quantity,

round((o.total_voucher * bi.persisted_price * bi.quantity) / total , 2)

case total when 0 then 0 else round((o.total_voucher * bi.persisted_price * bi.quantity) / total ,2) end

from order_order o, order_basketitem bi, (

select oo.id as o_id, sum(bii.persisted_price * bii.quantity) as total

from order_order oo, order_basketitem bii

where oo.id = bii.order_id

and oo.persisted_tracker_code = '127707332191417'

group by 1

) as xx

where o.id = bi.order_id

and o.persisted_tracker_code = '127707332191417'

and xx.o_id = o.id

Lastly - for association:

You will have to rename your returned column from the SQL so it match some other table's column in the LOAD statement or in the SQL

Re: Data Load Sql

petter — Mon, 05 Oct 2015 12:37:25 GMT

Re: Data Load Sql

Anonymous — Tue, 06 Oct 2015 05:33:41 GMT

Thank you so much, it worked perfect.

Now i'll try to optimize Load statement.