Add alphabets as a suffix

[#1]

Hi,

I want to add alphabets as a suffix ,only if the string value is appearing more than once in a field.

For Ex: the data is as shown in the below table.

Docid	price
150	25
150	30
150	45
25	30
200	10
200	12

I want the results as shown below. Suffix should be added only if the doc id is appearing more than once and count of doc id will not be same always.

Docid	price
150-a	25
150-b	30
150-c	45
25	30
200-a	10
200-b	12

Thanks in advance.

Regards,

Chaithra

[jagan · Accepted solution]

Hi,

Try like this

Temp:

LOAD *

INLINE [

Docid, price

150, 25

150, 30

150, 45

25, 30

200, 10

200, 12];

LEFT JOIN (Temp)

LOAD

Docid,

Count(Docid) AS Count

RESIDENT Temp

Group By Docid;

Data:

LOAD

*,

If(Not isNull(AsciiValue), Docid & '-' & Chr(AsciiValue), Docid) AS New_Docid;

LOAD

Docid,

price,

If(Peek(Docid) <> Docid AND Count > 1, 65, If(Peek(Docid) = Docid, Peek(AsciiValue) + 1, Null())) AS AsciiValue

RESIDENT Temp

ORDER BY Docid, price;

DROP TABLE Temp;

Regards,

Jagan.

[#3]

Hi Jagan,

Thanks for your response.

I am getting some error while joining the table.

Regards,

Chaithra

[jagan]

It is working correctly for me.  can u attach the script you are using.

[anbu1984]

T1:
Load *,AutoNumber(Docid+price,Docid) As RowNo Inline [
Docid,price
150,25
150,30
150,45
25,30
200,10
200,12 ];

T2:
Load Docid & If(Docid = Previous(Docid),'-' &Chr(96+RowNo),If(RowNo > 1,'-'&Chr(96+RowNo),'')),price Resident T1 Order By Docid,RowNo desc;

Drop Table T1;

[#6]

Thanks for the help, its working fine.

[#7]

Thank you. Even this method is giving the correct output.

[anbu1984]

My ans isn't helpful