3 Replies Latest reply: May 30, 2018 1:57 AM by Stef Rawoe RSS

    If multiple values/possibilities

    patrico mesri

      Hi Community.

      I would really appreciate some help with this. I couldn't think of anything better for the topic title..

       

      This is my data:

         

      Acreedor  IDCODE
      CL91037   272693254750070006130004069851                                     
      CL91038   202247126000070006130004069851                                     
      LO10061   307098777780070010820000199503                                     
      LO100612  307088041810070010820000199503                                    
      CL91013   202095632670070010830004006181                                    
      LO08530   202095632670070010830004006181                                     
      CL90620         0070018430004029351                                     
      LO09263   273164876400070018430004029351                                     
      LO09857   337122714990070019120000008792                                     
      LO10931   307120979450070019120000008792                                     
      CL91087   202229375240070019130004021429                                     
      LO10087   202229375240070019130004021429                                     
      LO10978   202838048100070020730004032600                                     
      LO109786  232589495790070020730004032600                                     

       

       

      What I need:

       

          

      OutputAcreedor  IDCODE
      SAME CODE, DIFFERENT IDCL91037   272693254750070006130004069851                                     
      SAME CODE, DIFFERENT IDCL91038   202247126000070006130004069851                                     
      SAME CODE, DIFFERENT IDLO10061   307098777780070010820000199503                                     
      SAME CODE, DIFFERENT IDLO100612  307088041810070010820000199503                                    
      SAME CODE, SAME IDCL91013   202095632670070010830004006181                                    
      SAME CODE, SAME IDLO08530   202095632670070010830004006181                                     
      SAME CODE, SAME IDCL90620         0070018430004029351                                     
      SAME CODE, SAME IDLO09263   273164876400070018430004029351                                     
      SAME CODE, DIFFERENT IDLO09857   337122714990070019120000008792                                     
      SAME CODE, DIFFERENT IDLO10931   307120979450070019120000008792                                     
      SAME CODE, SAME IDCL91087   202229375240070019130004021429                                     
      SAME CODE, SAME IDLO10087   202229375240070019130004021429                                     
      SAME CODE, DIFFERENT IDLO10978   202838048100070020730004032600                                     
      SAME CODE, DIFFERENT IDLO109786  232589495790070020730004032600                                     

       

      In case the output table is not self explanatory, I need to create a dimension with 3 possible outcomes:

       

      1. The "Acreedor" has one single ID for one single CODE (No examples in this table)

      2. The "Acreedor" has one single ID, but 2 or more CODES

      3. The "Acreedor" has 2 or more IDs, but one single CODE.

       

      Thank you in advance.
      Kind regards.

        • Re: If multiple values/possibilities
          Sumit Panda

          How are you getting the "SAME CODE, DIFFERENT ID" values ? What is the logic behind it? If you can explain that may be we can help you out.

          • Re: If multiple values/possibilities
            Stef Rawoe

            Create a temp table?

            - Acreedor, count distinct ID, count distinct CODE

            Join on your base table (just on the Acreedor field).

            You will have something like:

            AcreedorIDCODE

            NewCountID

            NewCountCode
            CL91037 272693254750070006130004069851                                    12
            CL91038 202247126000070006130004069851                                    12
            LO10061 307098777780070010820000199503                                    12
            LO100612307088041810070010820000199503         12

             

            From there, add new column with your logic?

            if(NewCountID > 1 and NewcountCode = 1, 'same code different ID',

                 if( ...

            • Re: If multiple values/possibilities
              Anil Samineni

              Not entirely sure about your request, But this is what i understand from explanation. Perhaps this?

               

              If(ID = Previous(ID), Peek('Output') & AutoNumberHash256(ID)) as Output