Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikAngel
Partner - Creator
Partner - Creator

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
Gysbert_Wassenaar

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

View solution in original post

13 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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

Gysbert_Wassenaar

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
jagan
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.

sunny_talwar

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

Capture.PNG

jagan
Luminary Alumni
Luminary Alumni

Check this link hope it helps you.

Optimized and Non Optimized loading

Regards,

Jagan.

sunny_talwar

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

Gysbert_Wassenaar

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
sunny_talwar

That make sense. Thanks for the explanation

Best,

Sunny