Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

extract customer name on load

Hi Experts

I have loads of data in a table column called TICKET which is formatted like this :

Contract Ref (16 Alphanumeric )
Space
Customer Number (9 digits)
Space
Customer Name ( Alphanumeric - Unknown Length)
Space
Inputter (5 numbers)
Space( Up until a string length of 70 in total)

In the script I have used left & mid Functions to create columns Contract Ref & Customer number

But now I need Customer Name, I'm sure its a combination of mid?Lefts etc , if I was to do this in Excel I'd use finds but I dont believe I can use them in Qlik,

See desired reults in table below -

Any suggestions really Appreciated

Thanks

A

DATALen(A1)DESIRED NAME
ABCDE12345600001 400000001 HOP SCOTCH LTD 10005                       70HOP SCOTCH LTD
ABCDE12345600003 400000002 ANNE MOONED 10006                          70ANNE MOONED
ABCDE12345600001 400000001 SPACESHIPS & SUITS TO LET 10007            70SPACESHIPS & SUITS TO LET
ABCDE12345600002 400000001  SPACESHIPS & SUITS TO LET 10007           70SPACESHIPS & SUITS TO LET
ABCDE12345600001 400000001 ROCKS ON 01203                             70ROCKS ON

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

trim(PurgeChar(mid(TICKET, 28, 42), '0123456789' )) as Result

View solution in original post

8 Replies
Gysbert_Wassenaar

try mid(TICKET,28,len(TICKET)-32)  


talk is cheap, supply exceeds demand
swuehl
MVP
MVP

Try

trim(PurgeChar(mid(TICKET, 28, 42), '0123456789' )) as Result

Clever_Anjos
Employee
Employee

Just use mid(TICKET,27), third parameter is optional, default is "until the end of string"

Not applicable
Author

Hi Guys

If I was to do this in Excel I would use the following formula

 

=REPLACE(LEFT(TRIM(B3),LEN(TRIM(B3))-6),1,FIND("|",SUBSTITUTE(B3," ","|",2)),"")

As I wish for my desired results not to have the last 5 numeric digits. Using the Mid function alone - although would allow the name to start after the Account adn Customer numbers it does not eliminate the Inputter Number,

Swuel suggestion worked a treat - only thing was if the customers name was - 123 ltd - the Result is now Ltd , although , we can live with that !!

Thanks to all for time

A

Not applicable
Author

Hi,

You can add a statement like this in the script

Trim(Replace(Replace(DATA,Left(DATA,27),Null()),Right(DATA,6),Null())) as CompanyName

PFA.

Hope that helps.

Regards,

-Khaled.

CELAMBARASAN
Partner - Champion
Partner - Champion

Try this

=left(Mid(TICKET,FindOneOf(TICKET,' ',2)),len(Mid(TICKET,FindOneOf(TICKET,' ',2)))-6)

swuehl
MVP
MVP

Swuel suggestion worked a treat - only thing was if the customers name was - 123 ltd - the Result is now Ltd , although , we can live with that !!

Right, this one should handle correctly both leading and trailing spaces spaces, the fixed set of codes characters at the beginning and end (and any alphanumeric characters, incl numbers, in the middle):

trim(mid(mid(trim(DATA),1,len(trim(DATA))-5),28)) as Result

Not applicable
Author

Thanks Khaled

That resolved the issue when the customer name has a number in it - appreciate all your help

Anne