14 Replies Latest reply: Sep 7, 2011 2:00 AM by ksmith22 RSS

    Mapping $Orphan

      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
          marcelo gonzalez

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

          Does anybody answered your question?

          Thank you.

          Marcelo (from Argentina)

            • Mapping $Orphan

              Hi Marcelo,

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

                • Mapping $Orphan
                  marcelo gonzalez

                  You are right.

                  I tried it and works..

                  Thank you very much.

                   

                   

                    • Mapping $Orphan
                      fesani

                      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

                      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.

                      Smile

                       

                        • Mapping $Orphan

                          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!Surprise

                            • Mapping $Orphan
                              Stephane Chivet

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

                                • Mapping $Orphan

                                   

                                   

                                   

                                   



                                   

                                   

                                  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 Sad



                                    • Mapping $Orphan

                                      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.

                          • Mapping $Orphan

                            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
                                Vipul Darji

                                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
                                    jvg

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