remove comma's at the end and extract month names in month feild

[Anonymous]

Hi all,.

i have 2 small requirments

1)  How to remove the commas at the end for salesperson values (Only some of the values are ending with commas - not all)

Salesperson

James,

Sam,

Smith

Some,

Sim,

South

Norw,

2) I want to extract month name from the month feild

Month:

201702

201703

201704

201705

Please help me on this

[vishsaggi]

Replace(Salesperson, ',','') AS SalesPerson

Month(Date#(MonthNames, 'YYYYMM')) AS Month_Names

OR

MonthName(Date#(MonthNames, 'YYYYMM')) AS Month_Names

OR

LOAD Month(Date#(MonthNames, 'YYYYMM')) AS Month_Names,

     Year(Date#(MonthNames, 'YYYYMM'))  AS Year Inline [

MonthNames

201702

201703

201704

201705

];

[neha_shirsath]

Removing commas-

You can use replace function or you can also use SubField(Salesperson,',',1)

For Monthname-

MonthName(Date#(Month,'YYYYMM'))

[Anil_Babu_Samineni]

Replace is not a good idea, Because If we replace with some other string as Null it consider as String. So, I don't believe the Replace() is nice one for this case and in future may be Nov 2017 version as they are consolidate some bugs in Analysis. Instead, Shall we go PurgeChar() .. Second one liked It !!!

[Anonymous]

go for subfield instead of replace ..if you use replace make sureto perform Rtrim

[johanlindell]

Hi,

To remove the occasional comma,

If (Right (Trim (Salesperson), 1) = ',',

     Mid (Trim (Salesperson), 1, Len (Trim (Salesperson)) - 1),

     Salesperson)

[vishsaggi]

Agreed. Thanks for that Anil.