topic Put comma after (x) characters in QlikView

Put comma after (x) characters

Mon, 20 Apr 2015 04:35:19 GMT

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

Re: Put comma after (x) characters

Peter_Cammaert — Mon, 20 Apr 2015 15:39:23 GMT

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

Re: Put comma after (x) characters

buzzy996 — Mon, 20 Apr 2015 15:53:46 GMT

see this,

Re: Put comma after (x) characters

Mon, 20 Apr 2015 22:55:06 GMT

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?

Re: Put comma after (x) characters

jduenyas — Mon, 20 Apr 2015 23:59:17 GMT

Is the data ALWAYS the same length?

Re: Put comma after (x) characters

rwunderlich — Tue, 21 Apr 2015 00:01:25 GMT

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'))

Re: Put comma after (x) characters

Tue, 21 Apr 2015 00:03:13 GMT

Hi Josh, no, they have different length

Re: Put comma after (x) characters

ramoncova06 — Tue, 21 Apr 2015 00:03:59 GMT

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

Re: Put comma after (x) characters

Anonymous — Tue, 21 Apr 2015 00:45:40 GMT

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;

Re: Put comma after (x) characters

Tue, 21 Apr 2015 01:16:44 GMT

Thanks for your input , it works