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

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

Xabinav — Sun, 21 Jan 2018 13:49:38 GMT

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

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

vishsaggi — Sun, 21 Jan 2018 14:10:11 GMT

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

];

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

Xabinav — Sun, 21 Jan 2018 15:05:04 GMT

Thanks

It's work

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

MarcoWedel — Sun, 21 Jan 2018 16:45:48 GMT

Hi,

another solution might be:

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

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

Xabinav — Sun, 21 Jan 2018 16:47:41 GMT

Thanks !