Discussion Board for collaboration on QlikView Scripting.
I'm trying to map to fields and I'm getting an $orphan field. I never got this before. Has someone an idea why this is
applymap('bnr',%BeID,'-') AS bxnr,
bxNR AS bxNR
The tables are currently already linked via %BelID.
-> found the problem: loading from qvd!!
I had the same problem and initialy thought my qlikview was corrupt. Then I found this blog, thanks for the idea, I got my report to work.
The funny thing is that my qlikview creates orphans when the mapping table points at a qvd. Funny thing is that I run same report in another machine and it doesnt create the orphan, so I guess there is a setting somewhere in qlikview that does that??.
Aloah! Thanks for posting the solution. I've long been bewildered as to why maps seem to work in some circumstances and not others. Never occured to me that it was because of a qvd!
Experienced the same issue on QV9 SR2 ... the RESIDENT load fixed the issue as indicated in the replies above. Was inconsistent on the same machine ... worked the first time, and then failed all subsequent times. Explored alrernatives such as an outer join - and while an option in our case for an accurate data model, the data volumes would cause the operation to time out, so was not feasible.
TAGS: Orphan, Load, ApplyMap, Resident, Outer Join
There seem to be an issue with optimised QVD loads forcing this behaviour. If you break the optimisation, for example by introducing a WHERE 1=1 clause, the map behaves itself. Test code below.
// Optimised QVD load leading to $orphan fields
// Uncomment WHERE 1=1 clause on mapping load to force non-optimised QVD load.
// This works, however ideally mapping loads should work from optimised QVD loads.
LOAD * INLINE [
STORE dim INTO dim.qvd (qvd);
DROP TABLE dim;
FROM dim.qvd (qvd)
//WHERE 1 = 1
LOAD *,applyMap('ValMap',id2) AS newID;
LOAD * INLINE [
Good note Jonas, will keep that in mind next time in happens. Usually the solution is easier than we could possibly imagine, and the easiest problems are suually the most difficult to resolve...