2 Replies Latest reply: Mar 6, 2017 1:47 AM by Agrim Sharma RSS

    delimit values

    Agrim Sharma

      Hi,

      i have a situation in which i want to get name of user(shown as bold below) , country&site name from a field 'DN'

      and want to count values starting from ADM_ but not that values which contains 'adm' from field 'Pre-W2K Name'

       

      DN
      CN=MIGR TEST,OU=TestUsers,OU=Users,OU=0060,OU=Lestrem,OU=France,DC=roquette,DC=corp,DC=priv
      CN=PECHIN Dominique,OU=Users,OU=Lestrem,OU=France,DC=roquette,DC=corp,DC=priv
      CN=HOUYELLE Dominique,OU=Users,OU=Lestrem,OU=France,DC=roquette,DC=corp,DC=priv
      CN=LCCOND-S3,OU=0030,OU=Lestrem,OU=France,DC=roquette,DC=corp,DC=priv
      CN=DUBAL Edith,OU=Users,OU=0011,OU=Lestrem,OU=France,DC=roquette,DC=corp,DC=priv

       

      i am using qlik sense.

        • Re: delimit values
          Tresesco B

          Try like:

           

          TextBetween( DN, 'CN=', ',') as [Site Name]

           

          Case sensitive search in qlik has some issues (bug). I have tried an alternate method. Few of the string functions in qlik are case sensitive, we can probably use it with a little trick and find a way of case sensitive search. May be some easier solution would be there too, some experts can suggest. The way I tried is like:

           

          Count( {<DN={"=SubStringCount(Replace(DN, 'ADM_', 'xxx'),'xxx')"}>} DN)

           

          Note: Assuming there would be no 'xxx' in your DN field, otherwise you can use anything that is not possible (may be like '%%%%') in the DN as sub string.

           

          Update:  Substringcount() itself is case sensitive, so we can remove the replace() and make it bit simpler, like:

           

          Count( {<DN={"=SubStringCount(DN, 'ADM_')"}>} DN)

            • Re: delimit values
              Agrim Sharma

              Count( {<[Pre-W2K Name]={"=SubStringCount([Pre-W2K Name], 'ADM_')"}>} [Pre-W2K Name]) this is completely correct thanks friend. you resolved my one query but in field DN i have uneven values here "OU" represents "country name" as well as many other things but i want only country name out of it like france,india



              but my concern is that:

               

              CN=ADM_DISSAUX Dominique,OU=Etudes et Dev,OU=Administrateurs,OU=Administration,DC=roquette,DC=corp,DC=priv
              CN=ADM_SAINT-CYR Nathalie,OU=Etudes et Dev,OU=Administrateurs,OU=Administration,DC=roquette,DC=corp,DC=priv
              CN=ADM_JOLY Frédéric,OU=SERAP,OU=Administrateurs,OU=Administration,DC=roquette,DC=corp,DC=priv
              CN=ADM_WESLINCK Florence,OU=Admins,OU=Users,OU=0028,OU=Lestrem,OU=France,DC=roquette,DC=corp,DC=priv
              CN=ADM_HOUTEKIET Cathy,OU=OCC_CORP,OU=Administrateurs,OU=Administration,DC=roquette,DC=corp,DC=priv
              CN=ADM_CANIVEZ Olivier,OU=Admins,OU=Users,OU=0028,OU=Lestrem,OU=France,DC=roquette,DC=corp,DC=priv
              CN=ADM_MARIE Michel,OU=SERAP,OU=Administrateurs,OU=Administration,DC=roquette,DC=corp,DC=priv

               

               

              thanks in advance    @Tresesco B