Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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?

Tags (2)
5 Replies
MVP
MVP

Re: Ignore 0 when load QVD, keep optimazed

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

bestofwest
Contributor II

Re: Ignore 0 when load QVD, keep optimazed

Inside Qlik it's not possible.

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

You must generate with QVD without duplicated lines.

suheshreddy
New Contributor II

Re: Ignore 0 when load QVD, keep optimazed

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
Contributor II

Re: Ignore 0 when load QVD, keep optimazed

Good try....

MVP
MVP

Re: Ignore 0 when load QVD, keep optimazed

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;

Community Browser