5 Replies Latest reply: Mar 26, 2012 10:31 AM by Anne Duffy RSS

    Mapping from a SubField

      Hi Guys,

       

      I am loading from an Excel doc that in a column Rejection Reasons has , in some instances multiple reason , see sample below :

       

      Rec NoCustomer Rejection Reasons
      1AnneA,B,
      2JohnA,
      3FredB,C,
      4NualaB,C,D,
      5TracyB,
      6StevenC,


      I have it loading as [Rejection Reasons],

      I also wish to able to identify number of instances of each as ReasonSubs, so for example

       

      LOAD RecID,

      trim(upper(subfield([Rejection Reasons], ','))) as ReasonSubs

      resident INPUT;

       

      if(RejectionReason =B,Count RejectionReason) and this would give a result of 1

      if(ReasonSubs =B,Count ReasonSubs) and this would give a result of 4

       

       

      all of the above works fine, my issue is I wish to categorise each of the rejection reasons

      So I have a map "RejectMap"

       

      ReasonCategory
      APaperwork
      BAuth
      CSecurity
      DFire

       

      When I apply the map :

      applyMap('RejectMap',trim(upper(subfield([Rejection Reasons], ','))),'Not catgorised yet') as RCMTCats

       

      When There is just one reason - it woprks fine

      But

      When there are multiple reasons, Qlikview applys every ReasonSub within the rejection Reason into each categories appropriate  -- So for example -

       

      Rec No 1 Category Paperwork

                                    A

                                    B

                     Category Auth

                                   A

                                   B

      Where I would wish for it to result as

       

      Rec No 1 Category Paperwork

                                   A

                     Category Auth

                                   B

       

       

      Can anyone Please help me on this

       

      Thanks a mill

       

      Anne

       

                  

             

           

        • Mapping from a SubField
          Henric Cronström

          The subfield function loops over the same record and picks out all subfields from a string, thus generating several records. If you have two subfield calls within the same Load, it will generate the (unlinked) cartesian product between the two result sets. And I think that that is waht happens here - you have one subfield call to create ReasonSubs and a second one to create RCMTCats.

           

          Try a preceding Load instead - so that the definition of RCMTCats is based on ReasonSubs:

           

          RejectMap:

          Mapping Load * inline

          [Reason          Category

          A          Paperwork

          B          Auth

          C          Security

          D          Fire] (txt, delimiter is '\t');

           

          Input:

          Load *,

                    applyMap('RejectMap',ReasonSubs,'Not catgorised yet') as RCMTCats;

          Load *,

                    Len(keepchar([Rejection Reasons],',')) as NoOfReasons,

                    trim(upper(subfield([Rejection Reasons], ','))) as ReasonSubs

          inline

          [Rec No          Customer          Rejection Reasons

          1          Anne          A,B,

          2          John          A,

          3          Fred          B,C,

          4          Nuala          B,C,D,

          5          Tracy          B,

          6          Steven          C,] (txt, delimiter is '\t');

           

          /HIC

            • Mapping from a SubField

              Thanks Henric

               

              I believe I understand now, the above works perfectly,

               

              Thanks


              A

              • Mapping from a SubField

                Hi Henric

                 

                May I ask A related question,

                 

                I had previously been counting the volume of customers by

                 

                '1' as Number,

                 

                but now if a customer has just say 4 reasons , it counts that customer 4 times,

                 

                I have a Rec() as RecID which does seem to be logging correctly

                 

                So I had wanted to go

                 

                Count(rec()) as Number,

                 

                This is not working ,


                Any suggestions please ??

                  • Mapping from a SubField
                    Henric Cronström

                    There are several ways to do this. One could be to use the [Rec No] that you have in the original table.

                    count([Rec No])   will evaluate to 4 for that customer, whereas

                    count(distinct [Rec No])   will evaluate to 1.

                     

                    Another way is to keep the data in two tables. Then you can count CustomerID from one table and RejectID from a second:

                     

                    Customers:

                    Load

                              [Rec No] as CustomerID,

                              Customer,

                              Len(keepchar([Rejection Reasons],',')) as NoOfReasons,

                              [Rejection Reasons]

                    From .....

                     

                    Rejects:

                    Load CustomerID,

                              RecNo() as RejectID,

                              applyMap('RejectMap',ReasonSubs,'Not catgorised yet') as RCMTCats;

                    Load

                              CustomerID,

                              trim(upper(subfield([Rejection Reasons], ','))) as ReasonSubs

                              resident Customers;

                     

                    /HIC