topic Re: Concat numbers in a different way in App Development

Concat numbers in a different way

Anonymous — Tue, 07 Mar 2017 08:13:03 GMT

I am using concat to show all values (and that can generate a long text, which Qlik Sense visually cut in the table by the way. A restriction of 255 characters?)

I use:

concat(Distinct PhotoNo,', ',PhotoNo)

Which gives (for example):

PhotoNo

1, 2, 3, 27, 29, 31, 32, 33, 34, 35, 40, 41, 42, 44

But I would like to get a result like this instead:

PhotoNo

1-3, 27, 29, 31-35, 40-42, 44

Anyway to do that?

Re: Concat numbers in a different way

Tue, 07 Mar 2017 11:39:52 GMT

Look at this. I think it's what you are looking for !

Number:
LOAD * INLINE [
Number
1
2
3
6
8
10
11
12
];

Sort:
LOAD
Number,
If(RowNo()=1,Number,
if(Previous(Number)+1=Number,1,0)) as test
Resident Number
Order By Number
;

tmp:
LOAD
Number,
test,
If(RowNo()=1,1,
If(Previous(test)=test and test=1,Peek(NO),Peek(NO)+1)) as NO
Resident Sort
;

DROP Table Sort;

Data:
LOAD
if(Count(Number)>1, Min(Number)&'-'&max(Number),Only(Number)) as Final
Resident tmp
Group By NO
;

Re: Concat numbers in a different way

sunny_talwar — Tue, 07 Mar 2017 12:14:31 GMT

Do you need this in the script or are you hoping to do this on the front end?