Concatenate Alphabet values followed by date value (Execution Column) based on the Another Column (ID).

[sruthi19]

Hi ,

Need to concatenate execution Column values based on the sameID Column.

When Concatenating  - first Alphabetical values (String)  followed with Date values should be concatenate.

Input:

Execution	ID
2022-02-21-01PM.(1234567)	1
Happy	1
2022-02-22-01PM.(3456987)	2
Test	2
2022-01-12-01PM.(6723456)	3
Generate	3
2022-01-12-01PM.(6723456)	4
System	4

Output:

Execution	ID
Happy 2022-02-21-01PM.(1234567)	1
Test 2022-02-22-01PM.(3456987)	2
Generate 2022-01-12-01PM.(6723456)	3
System 2022-01-12-01PM.(6723456)	4

[marcus_sommer]

You may use:

t1: load Execution, ID, recno() as RecNo from x;

t2: load ID, concat(Execution, ' ', -RecNo) as Execution
resident t1 group by ID;

- Marcus

[sruthi19]

Thanks for your quick response. I have updated my question with more clarity.

In Execution column we have 2 values with same ID. So i need to concatenate the 2 values , the out put should be first String value followed by date value.

[marcus_sommer]

My suggestion should work in general by string-concatenating the values. Without a third parameter in concat() the sorting of the values will be alpha-numeric which shouldn't work in regard to your requirement. This means you need to apply an appropriate numeric sorting-value - and here it depends on various aspects how to define it. Using recno() or rowno() may one way but there are also other methods thinkable, like fetching the first char-index with something like: ord(left(Execution, 1)) * -1 as Ord

- Marcus