Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

valerio_fatatis
New Contributor III

Concat Function: error for "many values" in the string

Hi,

i have a simple concat expr formula in a text box:

=Concat(ID,',') but return 21000 values (many...)

example: 123,234,345,456,567...

Its possible can split the string into regular blocks of max 100 values​​ (with var)?

exemple: (create a new string!)

(1,2,3,4,5,6,7,100);(101,102,103,104,105,106,107,200);(201,202,203,204,205,206,207,300) etc etc

thanks for all

1 Solution

Accepted Solutions
valerio_fatatis
New Contributor III

Re: Concat Function: error for "many values" in the string

Here is the solution ...

thank you anyway

=========================================

OBJECTS:

Load

          'T' as OT,

          'OBJECT' & RowNo() as OBJECTID,

          RowNo() as OBJECTIDPOS

AutoGenerate 25000;

$(vClass)=Mid(replace(Class(Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID),vSoglia),'<= x',''),  Index(replace(Class(Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID),vSoglia),'<= x',''),'< ')+2,10)

TextBox Expr:

=concat(OBJECTID & if(Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID)=$(vClass)-1 or Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID)=Count(TOTAL DISTINCT OBJECTID),');(')

,Chr(44))

6 Replies
jonbrough
Valued Contributor

Re: Concat Function: error for "many values" in the string

You can use a subfield to split the string up again within a loop and then rebuild it.

Better still:

1. set up a rowno() count in the field's table

2. load this table in during a loop with a where clause testing for this rowno() field being less then 100, 200, 300 ad infinitum

3. use you concatenate() function to build up the comma seperated string

Jonathan

valerio_fatatis
New Contributor III

Re: Concat Function: error for "many values" in the string

I explain.

The "resulting string" I need to run sql statement "script query at another db with extention control..." , which "DB settings" to not accept more than 100 values ​​in a string.

I have to break/split the original string (normal contat), and formatting it precisely so

(value1, value2, value3, value100);(valeu101,value102,value200); ...

I can not possible modify the load script...

jonbrough
Valued Contributor

Re: Concat Function: error for "many values" in the string

then are you are looking for a database or sql solution, as opposed to a qlikview scripting or functions solution ?

maybe a stored procedure on the db ?

jonathan

valerio_fatatis
New Contributor III

Re: Concat Function: error for "many values" in the string

My problem is being able to break a string created by a CONCAT expr too extensive.

I wish I could create a text string where every 1000 values submit the following digit value "   );(   "...

exemple:

NORMAL CONCAT: (1,2,3,4,5,6,7,... 211000) no good! many long...

IDEA....

(1,2,3,4,5,6,7,1000);(1001,1002,1003,1004,1005,2000);(3001,3002,3003,3004)...

thanks

jonbrough
Valued Contributor

Re: Concat Function: error for "many values" in the string

So you only need to read this back into another DB. It is not that you are reading from a DB and need this logic to be created outside of QlikView.

I don't understand why the advice I posted before is a problem then?

Jonathan

valerio_fatatis
New Contributor III

Re: Concat Function: error for "many values" in the string

Here is the solution ...

thank you anyway

=========================================

OBJECTS:

Load

          'T' as OT,

          'OBJECT' & RowNo() as OBJECTID,

          RowNo() as OBJECTIDPOS

AutoGenerate 25000;

$(vClass)=Mid(replace(Class(Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID),vSoglia),'<= x',''),  Index(replace(Class(Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID),vSoglia),'<= x',''),'< ')+2,10)

TextBox Expr:

=concat(OBJECTID & if(Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID)=$(vClass)-1 or Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID)=Count(TOTAL DISTINCT OBJECTID),');(')

,Chr(44))

Community Browser