5 Replies Latest reply: Jun 9, 2017 7:52 AM by Andrey Khoronenko RSS

    categorize values.

    Agrim Sharma

      Hi, all,

      I have a situation in which I have some patters of names, like below:-

      Pre-W2K Name-

      0101443Z

      0101444A

      01015693r

      0102101P

      0102120P

      0102196P

      0102197R

      0102213L

      0102218T

      ADM_9929390X

      ADM_9929500R

      ADM_9929503W

      ADM_9929560W

      ADM_9929626g

      ADM_ACCOUNT_RESERVE

      adm_arumtec

      ADM_AUTOCHECKDMZ

      ADM_BIPS

      ADM_CHECK_DMZ

      ADM_CONTPAQI_PROD

      ADM_CONTRACTORPBX

      ADM_CONTRACTORTIMECL

      ADM_COPERNIC

       

       

      I got “0101443Z" this type to calculate from below Script.

      Script-

      source: 

      LOAD *,

      IsNum(left("Pre-W2K Name",7)) as MatriAccounts,

      Match(Mid("Pre-W2K Name",8), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',

                                                                                                                       'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',

                                                                                                                       'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',

                                                                                                           'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'  )

      as MatriculeAccounts;

      LOAD

      "Pre-W2K Name"

      FROM [lib://AD Dashboard/AD-DATA-01.06.2017.xlsx]

      (ooxml, embedded labels, table is Sheet1); 

      NoConcatenate 

      Output:

      LOAD * 

      RESIDENT source

      WHERE MatriAccounts =-1 AND MatriculeAccounts >0; 

      DROP table source;

       

      I want to have

      "ADM_9929390X"

      "ADM_9929500R" as ADM MATRICULE ACCOUNTS, how to do this?

        • Re: categorize values.
          Andrey Khoronenko

          Hi,

           

          Two questions.

          1.

            .    .    .

          Output:

          LOAD *

          RESIDENT source

          WHERE MatriAccounts =-1 AND MatriculeAccounts >0;

          DROP table source;


          It is right? If the field value is empty, it can not be greater than zero (8 of its signs). On idea, [Output] table should not contain a records.

           

          2. Only these two values should be in the resulting table? What is the sign of the selection?

           

          Regards,

          Andrey

            • Re: categorize values.
              Agrim Sharma

              Hi Andrey,

               

              you are right for the first question

              for secon question- i want those namese wich start with ADM_ and have property of matriculate accounts.

              ie.

              I want to have

              "ADM_9929390X"

              "ADM_9929500R" as ADM MATRICULE ACCOUNTS, how to do this?


              can i share me complete script?

                • Re: categorize values.
                  Andrey Khoronenko

                  Hi,

                   

                  If I understood you correctly, may be like this

                   

                  LOAD*Inline

                  [Pre-W2K Name-

                  0101443Z

                  0101444A

                  01015693r

                  0102101P

                  0102120P

                  0102196P

                  0102197R

                  0102213L

                  0102218T

                  ADM_9929390X

                  ADM_9929500R

                  ADM_9929503W

                  ADM_9929560W

                  ADM_9929626g

                  ADM_ACCOUNT_RESERVE

                  adm_arumtec

                  ADM_AUTOCHECKDMZ

                  ADM_BIPS

                  ADM_CHECK_DMZ

                  ADM_CONTPAQI_PROD

                  ADM_CONTRACTORPBX

                  ADM_CONTRACTORTIMECL

                  ADM_COPERNIC

                  ]

                  Where Left([Pre-W2K Name-], 4) = 'ADM_' And RangeSum(Num(Mid([Pre-W2K Name-], 5, 6)))>0;

                   

                  Look attached file

                    • Re: categorize values.
                      Agrim Sharma

                      Hi Andrey,

                       

                      where should i keep this?

                      my scripts is as follows;-

                      A:

                      LOAD

                         "Pre-W2K Name",

                         if("Pre-W2K Name" like 'ADM*' , 'ADM',

                         if("Pre-W2K Name" like 'BAL*' , 'BAL',

                         if("Pre-W2K Name" like 'GEN*' , 'GEN',

                         if("Pre-W2K Name" like 'VC*' , 'VC',

                         if("Pre-W2K Name" like 'ADP*' , 'ADP',

                         if("Pre-W2K Name" like 'ROOM*' , 'ROOM',

                         if("Pre-W2K Name" like 'FOR*' , 'FORMATION',

                         if("Pre-W2K Name" like 'DESK*' , 'DESK',

                         if("Pre-W2K Name" like 'RT*' , 'ROUND TABLE',

                         if("Pre-W2K Name" like 'SUPPORT*' , 'SUPPORT',

                         if("Pre-W2K Name" like 'DEV*' , 'DEV',

                         if("Pre-W2K Name" like 'TEST*' , 'TEST',

                         if("Pre-W2K Name" like 'SVC*', 'SVC'))))))))))))) as ACCOUNTGROUP,

                         IF(IsNull(Role), 'Active Account','Inactive Account') as "Active Account" ,

                         if("Role" like 'DISABLED' , 'Disabled',

                         if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled/No Password',

                         if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired & no password require',

                         if("Role" like 'NO_PWDEXP' , 'No Password Expired',

                         if("Role" like 'NO_PWDEXP\ENCRYPT' , 'No password expire/Encrypted',

                         if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password/No Password Required',

                         if("Role" like 'NO_PWDEXP\SMART' , 'No Password Expire/Smart',

                         if("Role" like 'NO_PWDEXP\DELEGATE', 'No password expire/Delegated')))))))) as ACCOUNTSTATUS,

                         "E-Mail",

                         "Exchange Alias",

                         "Role",

                         "Canonical Name",

                         "Last Logon", 

                         Today(),

                         Date("Last Logon Time Stamp" )  as "Last login Time",

                      IF((Floor(Date(Today()) - Date("Last Logon Time Stamp", 'dd-mm-yyyy hh:mm:ss')))<=179 , 'RegularLogIn',

                      IF((Floor(Date(Today()) - Date("Last Logon Time Stamp", 'dd-mm-yyyy hh:mm:ss')))>179 , 'DelayLogIn')) as q,

                       

                         SubField("Canonical Name", '/' , 1) as Roquette,

                         SubField("Canonical Name", '/' , 2) as Country,

                         SubField("Canonical Name", '/' , 3) as Site,

                         SubField("Canonical Name", '/' , 4) as UserType,

                         SubField("Canonical Name", '/' , 5) as "Name of User",

                        "City",

                        "Region",

                        "State/Province",

                        "Expires",

                        "Object Class",

                       

                       

                        'Dashboard Last reloaded on - ' & reloadtime()

                             

                      FROM [lib://AD Dashboard/AD-DATA-01.06.2017.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                       

                      source: 

                      LOAD *,

                      IsNum(left("Pre-W2K Name",7)) as MatriAccounts,

                      Match(Mid("Pre-W2K Name",8), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',

                        'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',

                        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',

                            'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'  )

                                                   as MatriculeAccounts;

                      LOAD

                      "Pre-W2K Name"

                      FROM [lib://AD Dashboard/AD-DATA-01.06.2017.xlsx]

                      (ooxml, embedded labels, table is Sheet1); 

                       

                       

                      NoConcatenate 

                       

                      Output:

                      LOAD * 

                      RESIDENT source

                      WHERE MatriAccounts =-1   AND MatriculeAccounts >0; 

                      DROP table source;

                        • Re: categorize values.
                          Andrey Khoronenko

                          The fastest way is instead

                           

                          source:

                          LOAD *,

                          IsNum(left("Pre-W2K Name",7)) as MatriAccounts,

                          Match(Mid("Pre-W2K Name",8), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',

                            'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',

                            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',

                                'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'  )

                                                      as MatriculeAccounts;

                          LOAD

                          "Pre-W2K Name"

                          FROM [lib://AD Dashboard/AD-DATA-01.06.2017.xlsx]

                          (ooxml, embedded labels, table is Sheet1);

                           

                          NoConcatenate

                           

                          Output:

                          LOAD *

                          RESIDENT source

                          WHERE MatriAccounts =-1  AND MatriculeAccounts >0;

                          DROP table source;

                           

                          May be like this

                          LOAD

                          "Pre-W2K Name"

                          FROM [lib://AD Dashboard/AD-DATA-01.06.2017.xlsx]

                          (ooxml, embedded labels, table is Sheet1)

                          Where Left([Pre-W2K Name-], 4) = 'ADM_' And RangeSum(Num(Mid([Pre-W2K Name-], 5, 6)))>0;


                          It all depends on what you want to get in the first table [A] and what in the [Output] table, as well as how they should be related (if they should). Only by code is difficult to determine.

                           

                          May be fragment of source data and what you wont to get in result?