topic Re: extract customer name on load in QlikView

extract customer name on load

Mon, 19 Nov 2012 16:27:40 GMT

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

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

Re: extract customer name on load

Gysbert_Wassenaar — Mon, 19 Nov 2012 16:40:24 GMT

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

Re: extract customer name on load

swuehl — Mon, 19 Nov 2012 16:49:36 GMT

Try

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

Re: extract customer name on load

Clever_Anjos — Mon, 19 Nov 2012 16:51:23 GMT

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

Re: extract customer name on load

Mon, 19 Nov 2012 17:01:54 GMT

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

Re: extract customer name on load

Mon, 19 Nov 2012 17:17:04 GMT

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.

Re: extract customer name on load

CELAMBARASAN — Mon, 19 Nov 2012 20:11:46 GMT

Try this

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

Re: extract customer name on load

swuehl — Tue, 20 Nov 2012 08:51:35 GMT

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

Re: extract customer name on load

Tue, 20 Nov 2012 09:05:28 GMT

Thanks Khaled

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

Anne