topic Re: Range Concat or Something Similar in QlikView

Range Concat or Something Similar

sunny_talwar — Tue, 15 Mar 2016 17:26:32 GMT

Hi Team -

Is there a way to perform Range Concat in a chart?

Sample:

Date	Dim
01/01/2016	A
01/02/2016	B
01/03/2016	C
01/04/2016	D
01/05/2016	E

Output:

Date	Cumulative
01/01/2016	A
01/02/2016	A, B
01/03/2016	A, B, C
01/04/2016	A, B, C, D
01/05/2016	A, B, C, D, E

Thanks,

Sunny

Re: Range Concat or Something Similar

swuehl — Tue, 15 Mar 2016 17:37:58 GMT

You could try a straight table with dimension Date and as expression

=If(Rowno()>1,Above("Cumulative") & ', ') & Dim

Cumulative being the expression label

Re: Range Concat or Something Similar

sunny_talwar — Tue, 15 Mar 2016 17:41:06 GMT

Stefan what about doing a distinct concatenation?

Sample:

Date	Dim
01/01/2016	A
01/02/2016	B
01/03/2016	B
01/04/2016	D
01/05/2016	B

Output:

Date	Cumulative
01/01/2016	A
01/02/2016	A, B
01/03/2016	A, B
01/04/2016	A, B, D
01/05/2016	A, B, D

Re: Range Concat or Something Similar

Digvijay_Singh — Tue, 15 Mar 2016 18:12:48 GMT

This worked on sample for me -

If(RowNo()>1,Above(Cumulative) & if(not wildmatch(Above(Cumulative),'*'&Dim&'*'),',' & Dim),Dim)

Re: Range Concat or Something Similar

johnw — Tue, 15 Mar 2016 18:23:04 GMT

This works.

if(rowno()=1,Letter,above(Cumulative) & if(not index(above(Cumulative),Letter),',' & Letter))

Edit: Sorry, I even hit refresh, but still didn't see your reply before I posted, Digvijay Singh.

Re: Range Concat or Something Similar

Digvijay_Singh — Tue, 15 Mar 2016 18:37:30 GMT

I am your big fan You made it simpler, Thanks

Re: Range Concat or Something Similar

sunny_talwar — Tue, 15 Mar 2016 18:40:22 GMT

Hahahaha thanks for agreeing Digvijay‌, you answered first, but John's answer is definitely little more simpler. I wish I had the option to reward two correct answers . But I just know a way to get you the points

Re: Range Concat or Something Similar

rubenmarin — Tue, 15 Mar 2016 18:54:24 GMT

Hi Sunny, just in case some string are substrings of others, I will enclose all values between some delimiter characters in a hidden column and remove them in the visible column.

I've added a file in the inline table to check the behaviour:

01/12/2015,AA

Re: Range Concat or Something Similar

MarcoWedel — Thu, 24 Aug 2017 20:22:05 GMT

Hi Sunny,

not quite a new thread, but I wanted to contribute another solution nevertheless:

expressions:

Left(Concat(TOTAL Dim,',',Date),Index(Concat(TOTAL Dim,',',Date)&',',',',RowNo())-1)

Left(Concat(TOTAL Dim,',',Date),RangeSum(Above(Len(Dim)+1,0,RowNo()))-1)

using some rearranged data to test for correct order:

Marco

Re: Range Concat or Something Similar

sunny_talwar — Fri, 25 Aug 2017 00:20:43 GMT

marcowedel‌ your responses are always welcomed