Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
try mid(TICKET,28,len(TICKET)-32)
Try
trim(PurgeChar(mid(TICKET, 28, 42), '0123456789' )) as Result
Just use mid(TICKET,27), third parameter is optional, default is "until the end of string"
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
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.
Try this
=left(Mid(TICKET,FindOneOf(TICKET,' ',2)),len(Mid(TICKET,FindOneOf(TICKET,' ',2)))-6)
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
Thanks Khaled
That resolved the issue when the customer name has a number in it - appreciate all your help
Anne