Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi
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
;
Do you need this in the script or are you hoping to do this on the front end?