topic Re: Remove duplicates for the concatinated string in Textbox in QlikView

Remove duplicates for the concatinated string in Textbox

avinashkk696 — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have a year field with values 2001,2002,2003,2004,2005,2006,2007,2008,2009. My requirement is, when I select multiple years, I want to display the selected years along with their previous years. For example if I select 2003,2004,2005 I want to display 2002,2003,2004,2005 in the textbox. I could not bring the output with the concat function. Please help me on this.

Thanks in advance

Re: Remove duplicates for the concatinated string in Textbox

arvind_patil — Mon, 24 Jul 2017 12:39:55 GMT

HI Avinash,

You need to use concatenate function other wise concatenate operator like '&'.

Thanks,

Arvind Patil

Re: Remove duplicates for the concatinated string in Textbox

avinashkk696 — Mon, 24 Jul 2017 12:45:17 GMT

Hi Arvind,

I have used Concat(Year,',') which is giving me current selections. How can I get the previous years??

Re: Remove duplicates for the concatinated string in Textbox

rahulpawarb — Mon, 24 Jul 2017 12:50:35 GMT

Hello Avinash,

May be this:

=Num(Left(Replace(GetCurrentSelections(), 'Year: ', ''), 4)) - 1 & ', ' & Replace(GetCurrentSelections(), 'Year: ', ' ')

Regards!

Rahul Pawar

Re: Remove duplicates for the concatinated string in Textbox

Nagaraju_KCS — Mon, 24 Jul 2017 12:56:33 GMT

Nice Rahul,

=IF(GetFieldSelections(Year)>0,

Num(Left(Replace(GetCurrentSelections(), 'Year: ', ''), 4)) - 1 & ', ' & Replace(GetCurrentSelections(), 'Year: ', ' '),

'Select Year')

Re: Remove duplicates for the concatinated string in Textbox

arulsettu — Mon, 24 Jul 2017 13:08:58 GMT

may be like this

load *,

Previous(year) as previousyear;

Load * Inline [

year

2001

2002

2003

2004

2005

];

and expression in filter

previousyear&','& year

Re: Remove duplicates for the concatinated string in Textbox

effinty2112 — Mon, 24 Jul 2017 13:20:23 GMT

Hi Avinash,

Try this in a textbox:

=Concat({1}aggr(if(Not isnull(Below(Only(Year))),only({1}Year),Year),Year),',')

Cheers

Andrew

Re: Remove duplicates for the concatinated string in Textbox

stigchel — Mon, 24 Jul 2017 13:50:19 GMT

You can remove the duplicates by using DISTINCT in the concat function. For also displaying previous years even though not selected, you can use set analysis that includes years smaller than the max year seklected:

=Concat({<year={"<=$(=Max(year))"}>} DISTINCT year,',')

Note that that you will have to replace 'year' with the exact name of your field year including capitals

Edit: <= instead of <