topic Mapping $Orphan in QlikView

Mapping $Orphan

Mon, 07 Sep 2009 17:52:29 GMT

Hi *,

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
happening?

My code:

applymap('bnr',%BeID,'-') AS bxnr,

bnr:
Mapping LOAD
%BeID,
bxNR AS bxNR
FROM.....

The tables are currently already linked via %BelID.

Regards

Aloah

-> found the problem: loading from qvd!!

Mapping $Orphan

Wed, 16 Sep 2009 21:19:15 GMT

Hello!! Im having the same problem reading from qvd.

Does anybody answered your question?

Thank you.

Marcelo (from Argentina)

Mapping $Orphan

Wed, 16 Sep 2009 21:31:51 GMT

Hi Marcelo,

I made a resident Load on the 'qvd load'. Maybe not the best solution....

Mapping $Orphan

Sat, 19 Sep 2009 21:26:21 GMT

You are right.

I tried it and works..

Thank you very much.

Mapping $Orphan

Thu, 24 Dec 2009 03:05:18 GMT

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??.

Mapping $Orphan

Thu, 31 Dec 2009 03:48:55 GMT

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!

Mapping $Orphan

vdarji — Tue, 26 Jan 2010 18:25:50 GMT

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

Mapping $Orphan

Tue, 13 Jul 2010 10:49:36 GMT

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.

dim:
LOAD * INLINE [
id, val
1, A
2, B
3, C
];

STORE dim INTO dim.qvd (qvd);

DROP TABLE dim;

ValMap:
MAPPING LOAD
id,val
FROM dim.qvd (qvd)
//WHERE 1 = 1
;

txn:
LOAD *,applyMap('ValMap',id2) AS newID;
LOAD * INLINE [
id2, val2
1, A
2, B
3, C
];

Mapping $Orphan

Tue, 13 Jul 2010 11:02:23 GMT

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...

Mapping $Orphan

Tue, 21 Sep 2010 16:30:43 GMT

OMG! I have been BEATING my head against the wall on this today!!! Thank you, all!!!

Mapping $Orphan

Tue, 21 Sep 2010 17:27:51 GMT

Actually that's the only official solution.

QV says you can never make a mapping load form a qvd. The work-around is to do as Aloah suggests or an undocumented solution as Jonas suggests.

I always err on the side of caution & chose to load mapping from a resident table and never from a qvd directly.

Mapping $Orphan

Tue, 21 Sep 2010 17:42:47 GMT

That's fine but there is nothing that I can find in their documentation that tells you not to use MAPPING / APPLYMAP from a QVD. Would certainly have saved me a lot of headaches / time!

Mapping $Orphan

schivet — Mon, 06 Dec 2010 14:58:44 GMT

Thanks a lot the WHERE 1=1 was the right solution for me! But agree, nothing in the help...

Mapping $Orphan

Fri, 07 Jan 2011 16:00:02 GMT

Actually it is clearly given in the complete refrence manual. I noticed it only after reading this post. I cracked my head for 3 days to solve the problem

Mapping $Orphan

Wed, 07 Sep 2011 06:00:19 GMT

I am also glad I found this as it was causing me grief.

Agree that QlikView Help contents could more closely match the pdf version of the reference manual.

But I just did a search and it is present in the pdf:

page 319 of v9 reference manual

page 327 of v10 reference manual

********

Note Mapping load does not support the use of QVD files as input source. The

QVD must first be loaded to a table and then a mapping load can be made

from this table.