Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agrimroquette
Contributor II

categorize values.

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?

5 Replies
ahaahaaha
Honored Contributor

Re: categorize values.

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

agrimroquette
Contributor II

Re: categorize values.

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?

ahaahaaha
Honored Contributor

Re: categorize values.

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

agrimroquette
Contributor II

Re: categorize values.

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;

ahaahaaha
Honored Contributor

Re: categorize values.

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 and what in the [Output] table, as well as how they should be related (if they should). Only by cod...

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

Community Browser