topic Filter data during Load in QlikView

Filter data during Load

Tue, 14 Sep 2010 08:33:38 GMT

I am loading a small (50K) excel sheet and a large (2M) oracle table, I would like to only load the data in the oracle table that is common to both datasets thus reducing the load time. I know I could do this outside of Qlikview, just wondered if I was missing some option/script tip to do this?

Also, is a 2million row, 100 column considered big for qlikview to manage? Uusually I am looking at a good 3-4 hours to load this - any tips to improve performance or only load changes to the table not a full refresh each time?

Thanks in advance

Filter data during Load

suniljain — Tue, 14 Sep 2010 08:35:47 GMT

Is there any common primary key exist between oravle table and excel sheet ?

If exist then you can use left join to extract requires data.

Filter data during Load

Tue, 14 Sep 2010 08:43:01 GMT

Yes there is a PK common to both data sets - How would I use left join when one data is in excel the other is in oracle, do you have any sample scripts? What I am trying to do is reduce load time by only bringing in the matching 50K records in the 2M oracle table. Thanks.

Filter data during Load

Tue, 14 Sep 2010 08:46:00 GMT

if common key exit then you use inner join to find out common data both of table

Regards

Ashish

Filter data during Load

Tue, 14 Sep 2010 09:37:04 GMT

thanks but I am not sure if that helps my main objective of cutting down the load time... obviously I could load the excel into oracle, do a join and bring in the result but I wanted to see if qlikview has any shortcuts to do this as you load the data

Filter data during Load

suniljain — Tue, 14 Sep 2010 10:11:36 GMT

Another Method is

Load *

From excel data

concatenate

Load *

From Oracle data where(Pk1 exist in pk2)

Re: Filter data during Load

Wed, 04 Sep 2013 13:50:47 GMT

i`m having a similar need, I have 2 excel files, one of them is too big for a quick load, and I would like to load only the records in common in the files, i`m having a little problems in doing this, if anyone could help, i`d be glad.

Re: Filter data during Load

Wed, 04 Sep 2013 14:04:01 GMT

could you show the syntax?

Re: Filter data during Load

Fri, 06 Sep 2013 16:42:02 GMT

Hello,

You can try using this:

small:

LOAD

field1,

field2

FROM Excel_Small;

large:

LOAD

field1,

field3

FROM Excel_Large

where exists (field1,field1);

Thanks,

ASINGH

Re: Filter data during Load

qlikmsg4u — Fri, 06 Sep 2013 16:44:27 GMT

we can use exist() function for this..correct me if i'm wrong

Re: Filter data during Load

Clever_Anjos — Fri, 06 Sep 2013 16:46:45 GMT

I think that your query is taking too much time to run.

Maybe you should get your values from Excel and pass them as a parameter on Where clause of your SQL query.

Re: Filter data during Load

Fri, 06 Sep 2013 17:22:51 GMT

james, I solved my issue using this

LOAD

field1,

field2

FROM Excel_Small;

left join

LOAD

field1,

field3

FROM Excel_Large;

I hope it helps you