Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

adimiz123
New Contributor III

How to separate a long number with a dash "-" ?

Hi,

I have two types of values that I want to write differently, Numbers with 7 digits and Numbers with 8 digits.

For example:

Numbers with 7 : 1234567 I want him to be 12-345-67

Numbers with 8 : 12345678 I want him to be 123-45-678


How can i separate the numbers like that?

Thanks


1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: How to separate a long number with a dash "-" ?

One way is this, but are your numbers always have the length, 7 and 8?

Check below.

Table1:

LOAD *, Pick(Match(FieldLen, 7, 8), Left(Number,2)&'-'&Mid(Number,3,3)&'-'&Right(Number,2),

                                    Left(Number,3)&'-'&Mid(Number,4,2)&'-'&Right(Number,3)) AS NewNumField;

LOAD *, Len(Number) AS FieldLen INLINE [

ID, Number

1, 1234567

2, 12345678

3, 1233452

];

4 Replies
vishsaggi
Esteemed Contributor III

Re: How to separate a long number with a dash "-" ?

One way is this, but are your numbers always have the length, 7 and 8?

Check below.

Table1:

LOAD *, Pick(Match(FieldLen, 7, 8), Left(Number,2)&'-'&Mid(Number,3,3)&'-'&Right(Number,2),

                                    Left(Number,3)&'-'&Mid(Number,4,2)&'-'&Right(Number,3)) AS NewNumField;

LOAD *, Len(Number) AS FieldLen INLINE [

ID, Number

1, 1234567

2, 12345678

3, 1233452

];

adimiz123
New Contributor III

Re: How to separate a long number with a dash "-" ?

Thanks

It's work

Re: How to separate a long number with a dash "-" ?

Hi,

another solution might be:

QlikCommunity_Thread_288554_Pic1.JPG

table1:

LOAD *,

    Num(Number,Pick(WildMatch(Number,'???????','????????'),'00-000-00','000-00-000'),'.','-') as NumberFormat

Inline [

    Number

    1234567

    2345678

    3456789  

    12345678

    23456789

    34567890  

];

hope this helps

regards

Marco

adimiz123
New Contributor III

Re: How to separate a long number with a dash "-" ?

Thanks !