Put comma after (x) characters

[#1]

Hi all,

I am quite new with QlikView and already found some "road block"

Here is my sample data

Data     Number

A          101206308116

B          889784231

C          114852

The end result will be

Data     Number

A          101,206,308,116

B          889,784,231

C          114,852

So basically, put comma after 3 characters

Any guidance will be great appreciated

Thanks

[Peter_Cammaert]

For output of expressions in a table, see the Number tab. Using the Format Pattern input field, you can add whatever characters you like. The default US number formatting string (thousands separator = comma) will do.

For dimensions, you can use a simple calculated dimension with the num() function. Does the same as the Number tab.

Best,

Peter

[buzzy996]

see this,

[#4]

Hi all,

Thanks for all the reply, I may need to explain further

This is not for formatting purpose, but each 3 characters will have a different purpose.

The raw data is XXXxxxXXXxxx, I want to put split each 3 characters to XXX,xxx,XXX,xxx where XXX or xxx will be used for another purpose

I already have SSIS package (T-SQL) which process the data, I am just wondering if I can replicate it to QlikView Load Script

T-SQL as below

DECLARE @pos INT, @result VARCHAR(100);

   SET @result = (

  Select distinct substring(

  (

  Select rtrim (''+ST1.CCDESC)  AS [text()]

  From STG_ORDER_SALES ST1

  Where ST1.CCCODE like 'SALE%'

  ORDER BY ST1.CCCODE

  For XML PATH ('')

  ), 1, 1000) [OrderClass]

  From STG_ORDER_SALES ST2

  );

   SET @pos = 4 -- location where we want to put ,

   WHILE @pos < LEN(@result)+1

   BEGIN

       SET @result = STUFF(@result, @pos, 0, ',');

       SET @pos = @pos+4;

   END

Any suggestion?

[jduenyas]

Is the data ALWAYS the same length?

[rwunderlich]

If they are numbers, and multiples of 3 digits, you can cheat and use the number formatting routine to insert the commas like this:

=text(num(123456,'0,000'))

[#7]

Hi Josh, no, they have different length

[ramoncova06]

‌you can use subfield for this subfield('123,567,89', 1) will give you 123, etc...

[Anonymous · Accepted solution]

if it's alphanumeric, see if this works for you,

Table1:

Load

Data,

Number,

ceil(len(trim(Number))/3) as Len_Number

;

LOAD * INLINE [

    Data,Number

    A,101206308116

    B,889784231

    C,114852

];

Table2:

Load

Data,

Number,

IterNo()-Len_Number as Flag_Keep,

if(Data=peek(Data),peek(Number2)&','&mid(Number,(IterNo()*3)-2,3),mid(Number,(IterNo()*3)-2,3)) as Number2

Resident Table1

while IterNo()<=Len_Number

order by Data asc

;

drop table Table1;

Table3:

Load

Data,

Number,

Number2

Resident Table2

Where Flag_Keep=0

;

drop table Table2;

[#10]

Thanks for your input , it works