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

    delimit values

    Agrim Sharma


      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'


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