Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
see this,
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?
Is the data ALWAYS the same length?
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'))
Hi Josh, no, they have different length
you can use subfield for this subfield('123,567,89', 1) will give you 123, etc...
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;
Thanks for your input , it works