topic Re: Add alphabets as a suffix in QlikView

Add alphabets as a suffix

Tue, 12 May 2015 07:03:23 GMT

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

Re: Add alphabets as a suffix

jagan — Tue, 12 May 2015 07:47:35 GMT

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.

Re: Add alphabets as a suffix

Tue, 12 May 2015 09:19:38 GMT

Hi Jagan,

Thanks for your response.

I am getting some error while joining the table.

Regards,

Chaithra

Re: Add alphabets as a suffix

jagan — Tue, 12 May 2015 09:22:00 GMT

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

Re: Add alphabets as a suffix

anbu1984 — Tue, 12 May 2015 09:23:13 GMT

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;

Re: Add alphabets as a suffix

Tue, 12 May 2015 10:12:18 GMT

Thanks for the help, its working fine.

Re: Add alphabets as a suffix

Tue, 12 May 2015 10:18:12 GMT

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

Re: Add alphabets as a suffix

anbu1984 — Tue, 12 May 2015 11:01:36 GMT

My ans isn't helpful