Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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;
When you load a qvd and use a condition during load (Where) the loading is not mor optimized...
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
Hi,
If you use where condition in the load statement then the QVD will not load in Optimized mode.
Regards,
Jagan.
Link to the blog: http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/
I think Where Exists might work, but Where not Exists might be the issue causing it to be un-optimized.
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;
That make sense. Thanks for the explanation
Best,
Sunny