Arrange a number in ascending order

[erric3210]

Hi Community,

I'm working on a tricky scenario.

Here it is.

I've below numbers:

LOAD * INLINE [

Number

62517

34

2174

];

Desired Output Required:

Number

12567

34

1247

If anyone has faced such issue then please share your suggestions.

Regards,
Eric

[E_Røse · Accepted solution]

It might not be the optimal solution, but I think this works:

// Define a mapping table that will be used to add a delimiter between each digit
map:
mapping LOAD * 
Inline [
old, new
1, 1-
2, 2-
3, 3-
4, 4-
5, 5-
6, 6-
7, 7-
8, 8-
9, 9-
0, 0-
];

//Apply the mapping table and create a group number(rowno) for each Number
tmp:
LOAD rowno() as rowno, MapSubString('map', Number) as separatedDigits  INLINE [
Number
62517
34
2174
];

// Split Number into digits
NoConcatenate
digits:
load rowno, subfield(separatedDigits, '-') as digit resident tmp;

// Conatenate the digits with digit as a sort weight (3rd parameter in concat)
NoConcatenate
data:
load concat(digit, '',digit) as SortedNumber resident digits 
group by rowno;

drop tABLE tmp, digits;

Please like and mark my answer as a solution, if it resolved your issue.

[erric3210]

Hi @E_Røse 

Thanks for working on the solution & sharing it.

I've used Inline tables & Mapsubstring() earlier.

Concat() will make the code lengthier.

Also, the original data has multiple dimensions.

Is there any other way apart from this approach.

Thanks again.

Regards,

Eric

[Kushal_Chawda · Accepted solution]

try below

Data:
LOAD *, len(Number) as Len 
INLINE [
Number
62517
34
2174
];

Left Join(Data)
Load Number,
     Concat(Digit,'',Digit) as Number2
Group by Number;
Load Distinct Number,
     mid(Number,IterNo(),1) as Digit
Resident Data
while IterNo()<=Len;

[E_Røse]

You could try  a brute fore solution using the keepchar- function.

Something like

Keepchar(Number, '0')&keepchar(Number,'1')&...

and so on. Answering from my phone, so did not test it.. you might need to combine each keepchar with a Coalesce if it returns null(?)

Please like and mark my answer as a solution, if it resolved your issue.

[MarcoWedel · Accepted solution]

similar to the already proposed KeepChar() solution, just using different functions:

tabNumbersSort:
LOAD *,
     Repeat(0,SubStringCount(Number,0))&
     Repeat(1,SubStringCount(Number,1))&
     Repeat(2,SubStringCount(Number,2))&
     Repeat(3,SubStringCount(Number,3))&
     Repeat(4,SubStringCount(Number,4))&
     Repeat(5,SubStringCount(Number,5))&
     Repeat(6,SubStringCount(Number,6))&
     Repeat(7,SubStringCount(Number,7))&
     Repeat(8,SubStringCount(Number,8))&
     Repeat(9,SubStringCount(Number,9))as NumberSort
INLINE [
	Number
	62517
	34
	2174
];

[erric3210]

Thank You @E_Røse @Kushal_Chawda @MarcoWedel for sharing your solutions.

All solutions are correct  & giving right results as per my requirement.

I need to use any one out of 3. So, I'm using @MarcoWedel solution.

Reason behind using it. No need to resident load tables. It is short.

Many thanks. It was a great learning experience.

Regards,

Eric