Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
johnsmithqv
New Contributor III

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

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

6 Replies
Highlighted
vishsaggi
Esteemed Contributor III

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

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

];

Highlighted
neha_shirsath
Valued Contributor

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

Removing commas-

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

For Monthname-

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

Highlighted

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

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 !!!

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
johnhhtp2
Contributor II

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

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

Highlighted
Partner
Partner

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

Hi,

To remove the occasional comma,

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

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

     Salesperson)

Highlighted
vishsaggi
Esteemed Contributor III

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

Agreed. Thanks for that Anil.