topic Re: Load data without where condition in optimize way in QlikView

Load data without where condition in optimize way

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

hello

i have 1 table which is contain South, North, West, East data,i want to show data for only North region but i dont want to used where condition in it then how it is possible by using optimize way.

Thanks..

Re: Load data without where condition in optimize way

RSvebeck — Sat, 27 May 2017 20:58:53 GMT

We need more information to answer your question.

Re: Load data without where condition in optimize way

Anonymous — Sat, 27 May 2017 21:08:05 GMT

i have 1 table

region:

LOAD South,

North,

West,

east

FROM News;

i want to show data only from North region in optimize way without using where condition.

Re: Load data without where condition in optimize way

RSvebeck — Sat, 27 May 2017 21:13:12 GMT

ok. but optimezed for what purpose? script execution time? Server memory? Client performance?

Re: Load data without where condition in optimize way

Anonymous — Sat, 27 May 2017 21:28:54 GMT

ok leave it that optimize, just tell me only without where

Re: Load data without where condition in optimize way

RSvebeck — Sat, 27 May 2017 21:35:30 GMT

Its dificult to understand your question, and why using "Where" is not an option.

but from you example code - if dimensions west, east and south are to be ignored, just do a distinct load and remove dimensions not wanted.

load Distinct

north

from news;

Re: Load data without where condition in optimize way

Anonymous — Sat, 27 May 2017 21:46:29 GMT

actually i faced this scenario in interview

Re: Load data without where condition in optimize way

RSvebeck — Sat, 27 May 2017 21:51:44 GMT

ah, ok.

perhaps they mean you want to keep qvd-optimized load but the Where clause causes the load be not qvd-optimimized... its a normal interview question...

if this is what they mean, the best practice is to do this, it will be very fast load:

temp_opt:

load region, field1, field2,field3 from news.qvd;

news:

noconcatenate

load field1,field2, field3 resident temp_opt where region='North';

drop table temp_opt;

Re: Load data without where condition in optimize way

Anonymous — Sat, 27 May 2017 22:10:44 GMT

but he said without using any condition in where clause.

Re: Load data without where condition in optimize way

RSvebeck — Sat, 27 May 2017 22:20:47 GMT

hmmm....

i guess you could potentially write a if statement in the script to force all non North transactions to become Null values but that would make no sense, and would just slow down the load process alot...

....without a where clause in the load script, all loaded data will exist in the data model after the load is complete, and exclusions of regions will have to be done in the user-interface instead by using on-open-triggers or by more or less forced user selections, or in chart expressions by using set-analysis.

Re: Load data without where condition in optimize way

ziadm — Sat, 27 May 2017 22:23:26 GMT

I think what the question is to use Inner Join

region:

LOAD South,

North,

West,

east

FROM News;

inner Join Load

North

from Table2;

Re: Load data without where condition in optimize way

Anil_Babu_Samineni — Sun, 28 May 2017 03:31:36 GMT

We have different approach even without WHERE clause, Can you explain proper manner whats your intention. Does this is Field or Some Value?

Re: Load data without where condition in optimize way

its_anandrjs — Sun, 28 May 2017 06:45:51 GMT

Hi,

What is the mean for this

region:

LOAD South, North, West, east

FROM News;

do you mean there is separate fields for the regions in your data base table please provide more info for this, if so then load only North field.

Ex:-

region:

LOAD North

FROM News;

Regards

Anand

Re: Load data without where condition in optimize way

Anonymous — Sun, 28 May 2017 07:04:00 GMT

first tell me one thing, which query known as optimized and which is unoptimized with example..

thank you

Re: Load data without where condition in optimize way

RSvebeck — Sun, 28 May 2017 07:07:18 GMT

You find most things about QVD optimization in this great blog post:

https://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

Re: Load data without where condition in optimize way

Digvijay_Singh — Sun, 28 May 2017 07:07:24 GMT

Check this -

What is optimised QVD?

Re: Load data without where condition in optimize way

avinashelite — Sun, 28 May 2017 07:17:59 GMT

QVD load without where clause is called the Optimized load

QVD load with where clause (except the exists option) is called the un-Optimized load

I feel his intention was to get the answer to use the exits key word

Re: Load data without where condition in optimize way

Kushal_Chawda — Sun, 28 May 2017 07:39:57 GMT

Do like this

First load the value through inline table which you want to filter

Region:

LOAD * inline [

Region

North ];

Data:

LOAD *

FROM Table

where exists (Region);

Note:

Field Name of inline table should match with the name in Actual table

If in your data table region field value in upper case, in inline table also you need to write value in upper case.

In above table only rename field is allowed to make the load optimize (if condition or any operation on field is not allowed for optimize load). where exists (Region) is already optimize.

Re: Load data without where condition in optimize way

RSvebeck — Sun, 28 May 2017 07:54:41 GMT

I hope he was not aiming for an single answer but rather for a better and deeper understanding on your Qlik script skill level by seeing how you were reasoning around optimized loads, where clauses and data modelling.

There is some basic strategies I use when loading big amounts (>50m rows) of data as fast as possible into QV.

1. Load biggest tables first

2. Always do optimized loads first, and after (if needed) load them resident with complex where clauses.

3. Drop unused tables asap

4. Drop unused fields asap

5. Group by and join with as little data as possible as late as possible.

There are a lot if details on every step, especially step 2: Where exist can sometimes work, still keeping optimized load. Concatenate can be optimized in some scenarios.

Re: Load data without where condition in optimize way

Anonymous — Sun, 28 May 2017 08:10:40 GMT

news:

LOAD South,

North,

West,

east

FROM region

where exists(North);

you mean above load is Optimized load?

i tried above load but after reloading it doesn't show data.