topic Re: Not Exists() in QlikView

Not Exists()

QlikAngel — Thu, 02 Jul 2015 10:35:26 GMT

Hi,

I am facing issue while using where not exists function.. its not giving an optimized load.

INLINE:

LOAD * INLINE

[

4271

3838

];

Dealers:

LOAD ID as DealerId,

ID as DealerIdSurveyor,

ID as DealerId_AXA,

Organization as Dealer,

//AreaId,

//CityId ,

StateId ,

TC_DealerTypeId

FROM

$(vpath)\Dealers.qvd

(qvd) where not Exists (ID);

Re: Not Exists()

alexandros17 — Thu, 02 Jul 2015 10:43:59 GMT

When you load a qvd and use a condition during load (Where) the loading is not mor optimized...

Re: Not Exists()

Gysbert_Wassenaar — Thu, 02 Jul 2015 10:44:28 GMT

You're renaming ID to something else: ID as DealerId. That's causing a non-optimised load.

See this blog post: Optimized QVD Loads: caveats for using the Exists() function « BI Commons

Re: Not Exists()

jagan — Thu, 02 Jul 2015 10:45:58 GMT

Hi,

If you use where condition in the load statement then the QVD will not load in Optimized mode.

Regards,

Jagan.

Re: Not Exists()

sunny_talwar — Thu, 02 Jul 2015 10:47:17 GMT

Gysbert‌ according to stevedark‌'s blog you can rename the fields

Re: Not Exists()

jagan — Thu, 02 Jul 2015 10:47:43 GMT

Check this link hope it helps you.

Optimized and Non Optimized loading

Regards,

Jagan.

Re: Not Exists()

sunny_talwar — Thu, 02 Jul 2015 10:47:59 GMT

Link to the blog: http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

Re: Not Exists()

sunny_talwar — Thu, 02 Jul 2015 10:51:14 GMT

I think Where Exists might work, but Where not Exists might be the issue causing it to be un-optimized.

Re: Not Exists()

Gysbert_Wassenaar — Thu, 02 Jul 2015 10:51:40 GMT

Yes, you can rename fields. But if you use where exists or where not exists then the field needs to be included with its original name in the lists of fields to be loaded in order to still have an optimized load.

This should be an optimized load:

INLINE:

LOAD * INLINE

[

4271

3838

];

Dealers:

LOAD

ID,

ID as DealerId,

ID as DealerIdSurveyor,

ID as DealerId_AXA,

Organization as Dealer,

//AreaId,

//CityId ,

StateId ,

TC_DealerTypeId

FROM

$(vpath)\Dealers.qvd

(qvd) where not Exists (ID);

DROP FIELD ID FROM Dealers;

Re: Not Exists()

sunny_talwar — Thu, 02 Jul 2015 10:53:14 GMT

That make sense. Thanks for the explanation

Best,

Sunny

Re: Not Exists()

Thu, 02 Jul 2015 11:07:18 GMT

Also, Deriving teh same field more than once causes not optimized.

Re: Not Exists()

QlikAngel — Thu, 02 Jul 2015 11:27:33 GMT

Hi Gysbert,

Its worked as Optimized.. thanks a ton.

Re: Not Exists()

stevedark — Thu, 02 Jul 2015 18:31:18 GMT

I'm surprised that renaming the one field to four different names doesn't break Optimized load - that must have been a change at some point (I'm going to have to test again!).

Another option is to do the WHERE EXISTS with the name as it appears in the QVD and then use RENAME FIELD after the event.

Steve

Re: Not Exists()

mena_ricardo — Fri, 18 Jan 2019 14:22:38 GMT

Hi,

I think I do the same, but it doesn´t work:

REG:

LOAD
n_contr,
n_contr as n_contr_num
FROM op.QVD (qvd)
where suc=1500 or suc=5505;

AVAL_NO_REG:

LOAD
n_contr as n_contr,
Code,
suc
FROM 1500.QVD (qvd)
Where not Exists(n_contr_num);

drop table REG;

store AVAL_NO_REG into xxxxxxxxxx;
drop table AVAL_NO_REG;

Can you help me, please?

Thanks