Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Anuhyak1
New Contributor

Add to place character - after first 5 digits of a field

a) I have  a style field in database. I need to place  -  after 1st five digits or characters. 

Actual Style Expected Style
1EA45EMN51EA45-EMN5
2BA45MNP62BA45-MNP6
8cde1HJK2C8cde1-HJK2C

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Add to place character - after first 5 digits of a field

In case you have more digits in the field:

Value:

load Actual,index(Actual,5)as Index,left(Actual,5)&'-'&right(Actual,len(Actual)-5) as Expected inline [
Actual
1EA45EMN5
2BA45MNP6
8cde1HJK2C
aaaaaaaaaaa
];

View solution in original post

5 Replies
Highlighted
Partner
Partner

Re: Add to place character - after first 5 digits of a field

Try this:

Value:
load Actual,left(Actual,5)&'-'&right(Actual,5) as Expected inline [
Actual
1EA45EMN5
2BA45MNP6
8cde1HJK2C
];

Highlighted
Partner
Partner

Re: Add to place character - after first 5 digits of a field

In case you have more digits in the field:

Value:

load Actual,index(Actual,5)as Index,left(Actual,5)&'-'&right(Actual,len(Actual)-5) as Expected inline [
Actual
1EA45EMN5
2BA45MNP6
8cde1HJK2C
aaaaaaaaaaa
];

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

Re: Add to place character - after first 5 digits of a field

or maybe just 

mid(Actual, 6) for the right-hand side.

-Rob

Highlighted
Anuhyak1
New Contributor

Re: Add to place character - after first 5 digits of a field

Thanks for the reply. But, In the style field  We are getting 12 characters some times in style field  and 8 characters  . In that case some characters are truncating if you hard code. I just need '- 'after 5 characters from left. 

I have used below expr in chart 

=left(STYLE,4)&'-'& right(STYLE,5)

STYLE  
P3SoQt4368P3so-t4368Q is truncating
BETR5678BETR-5678correct 

 

 

Highlighted
MVP & Luminary
MVP & Luminary

Re: Add to place character - after first 5 digits of a field

=left(STYLE,4)&'-'& mid(STYLE,5)

-Rob