Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ignore 0 when load QVD, keep optimazed

Hello Guys!

I have really huge amount of data (one qvd file is about 250 million of rows, and I need to load and process several files). The problem is that these qvd files contains duplicates of rows.

For example I have two rows for the same Item. One row contains Sum of sales and 0 as a Price, but the other one contains 0 in Sales and positive Price.

ItemPriceSumOfSales
100010100
1000110

I want to reduce data by joining parts of the same table:

t1:

LOAD

     Item,

     Sum

From QVD Where Sum>0;

left join (t1)

LOAD

     Item,

     Price

From QVD Where Price>0;

But if i use "where" in a clause it breaks optimized load and all my data loads terrifying slow.

Can somebody advice me how to keep optimized load and keep only rows with positive values?

5 Replies
afurtado
MVP
MVP

Hi.

I don't see a way to keep optimized....Maybe change the script (script that you store QVD) to save in one record is the best way, otherwise

where

or

group by resident

but time for huge data will be big.....

Best,

Alessandro Furtado

furtado@farolbi.com.br
bestofwest
Creator II
Creator II

Inside Qlik it's not possible.

When use "where" the optimized option is not used.

You must generate with QVD without duplicated lines.

suheshreddy
Contributor III
Contributor III

Why don't you use where exists function with will remain optimized load and can achieve your logic.

Use an temporary inline table and do where exists..

bestofwest
Creator II
Creator II

Good try....

maxgro
MVP
MVP

load from qvd in this script is always optimized

f: load SumOfSales, Price from t1.qvd (qvd);

s: load SumOfSales Resident f where SumOfSales>0;

p: load Price Resident f where Price>0;

DROP Table f;

t:

load Item, SumOfSales

from t1.qvd (qvd)

where exists(SumOfSales);

Left join (t)

load Item, Price

from t1.qvd (qvd)

where exists(Price);

DROP Table s,p;