Using Previous/Peek to Create Unique Usernames

Thu, 24 Mar 2011 14:26:36 GMT

Greetings!

This is very simple, but I cannot do that in QV code...

I have a filed EMPLOYEE, in which I have full names, like

EMPLOYEE
------------------------------------
THIAGO RIBEIRO
ALINE SANTOS
ERIC LENSHER
CHARLES XAVIER
.
.
.

I am creating a USERNAME to use within Section Access, with this function:

LEFT(EMPLOYEE,1) & SUBFIELD(EMPLOYEE, ' ', 2) as USERNAME,

And it works fine, until this situation happens...

EMPLOYEE
------------------------------------
ERIC LENSHER
ERICA LENSHER JR
ERIC LENSHER XAVIER JR
.
.
.

My function creates ELENSHER three times, and it messes up the whole thing. I tried this treatment after the USERNAME field is created...

LET COUNTER = 0;

IF(USERNAME= PREVIOUS(USERNAME), USERNAME & ($(COUNTER ) + 1), USERNAME) as USERNAME

In order to create such results

USERNAME
-----------------------------------------
ELENSHER
ELENSHER2
ELENSHER3
.
.
.

But it isn't working well... any light to solve this?

Thax everyone!

Using Previous/Peek to Create Unique Usernames

prieper — Thu, 24 Mar 2011 20:57:25 GMT

Give it a try with

RawData:
 LOAD
 EMPLOYEE,
 LEFT(EMPLOYEE,1) & SUBFIELD(EMPLOYEE, ' ', 2) as USERNAME
 INLINE
 [EMPLOYEE
 THIAGO RIBEIRO
 ALINE SANTOS
 ERIC LENSHER
 CHARLES XAVIER
 ERIC LENSHER
 ERICA LENSHER JR
 ERIC LENSHER XAVIER JR];

Data:
 LOAD
 *,
 IF(COUNTER = 1, USERNAME, USERNAME & COUNTER) AS USERNAME_corr;
 LOAD
 *,
 IF(USERNAME <> PEEK(USERNAME), 1, PEEK(COUNTER) +1) AS COUNTER
 RESIDENT
 RawData
 ORDER BY
 USERNAME,
 EMPLOYEE;

 DROP TABLE RawData;

HTH
Peter

topic Using Previous/Peek to Create Unique Usernames in QlikView

Using Previous/Peek to Create Unique Usernames

Using Previous/Peek to Create Unique Usernames