Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

Not Exists()

Hi,

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

INLINE:

LOAD * INLINE

[

ID

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);

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Not Exists()

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

[

ID

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;


talk is cheap, supply exceeds demand
13 Replies

Re: Not Exists()

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

MVP & Luminary
MVP & Luminary

Re: Not Exists()

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


talk is cheap, supply exceeds demand
MVP & Luminary
MVP & Luminary

Re: Not Exists()

Hi,

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

Regards,

Jagan.

Re: Not Exists()

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

Capture.PNG

MVP & Luminary
MVP & Luminary

Re: Not Exists()

Check this link hope it helps you.

Optimized and Non Optimized loading

Regards,

Jagan.

Re: Not Exists()

Re: Not Exists()

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

MVP & Luminary
MVP & Luminary

Re: Not Exists()

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

[

ID

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;


talk is cheap, supply exceeds demand

Re: Not Exists()

That make sense. Thanks for the explanation

Best,

Sunny