Unable to load data in QVD

[sujoybose]

I am trying to run a heavy SQL query (which takes 170 seconds to run on Mysql server)  on QLIK, but the data isn't loading, even after 40 minutes of activity. And loses connection.

I tried multiple times, checking internet connectivity, VPN connection, but nothing helped.

Can someone please tell me the possible reason for this failure.

[HirisH_V7]

• My Suggestion is to write & run that query in qlik script, after calling all that tables into qlik and converting them into QVD's. 
• As DB Data Extraction will have time out set, that may also cause dis-connection.

Even i have came across such issues in my previous apps. i did like above, it saved me lot of time and efforts.

[mahaveerbiraj]

Hi Sujoy ,

Could you please mention your query  or  attach  qliksense app

[HirisH_V7]

Actually i did it long back,  for now doesn't have one to share. So all those SQL queries running in qlik should be rewritten-ed , Thus We need split line by line and rewrite the same in qlik.. Hope you got, what to be done.

[sujoybose]

select z.*
,ce.Parent_Category,
ce.cancelled_per,
ce.paymode,
re.Parent_Category,
re.returned_per,
rtoe.Parent_Category,
rtoe.rtoed_per
from
(
select order_id,cast(y.paymode as char) paymode,date(success_date) as dated,x.product_size_id,
Product_name,product_status,child_category_name,
parent_category_name from

(
select
product_size_id,
size,
Product_Id,
Product_name,
product_price,
mrp,
all_offer_price,
product_status,
product_launched_at,
base_inventory,
ptype,
stock_factor,
sales_factor,
member_price,
product_offer_type_id,
product_offer_type,
child_category_id,
parent_category_id,
category_gender,
child_category_name,
parent_category_name,
sleeve,
neck,
subtype,
cat_type,
subclass,
brand,
model,
x_factor,
y_factor,
cogs,
category_status,
design_id,
design_name,
design_status,
design_created_at,
design_updated_at,
designer_name,
color_name,
parent_color_name,
child_color_name,
stock_quantity,
product_size_qty_available
from product
)
x
right join
(
select
order_id,
product_size_id,
parent_id,
payment_gateway_enum,
payment_gateway_name,
order_status_enum,
order_status_name,
device_type,
shipping_money,
cod_money,
case when is_cod=0 then 'prepaid' else 'cod' end as paymode,
user_id,
success_date,
order_product_price,
offer_type,
offer_type_enum,
is_loyalty_order,
original_price,
city,
state,
hra_classification,
net_paid,
NonPromotionalWallet_debit_amount,
PromotionalWallet_debit_amount,
order_deductions_amount,
packing_date,
shipping_date,
delivered_date,
rto_date,
rto_initiated_date,
date(success_date) as dated
from order
where order_id=28644521 -- success_date >= '2019-08-01'
)
y
on x.product_size_id=y.product_size_id
)z
left join cancelled ce
on ce.date=z.dated and ce.Parent_Category=z.parent_category_name and ce.paymode = z.paymode
left join return re
on re.date=z.dated and re.Parent_Category=z.parent_category_name
left join rto rtoe
on rtoe.date=z.dated and rtoe.Parent_Category=z.parent_category_name and rtoe.paymode = z.paymode
;

[sujoybose]

Hi Hirish,

I tried joining the QVD's but it gave me an error of circular logic.

Regards,

Sujoy