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

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

valerio_fatatis — Wed, 05 Jun 2013 10:22:44 GMT

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

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

Anonymous — Wed, 05 Jun 2013 11:26:13 GMT

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

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

valerio_fatatis — Wed, 05 Jun 2013 12:01:14 GMT

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

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

Anonymous — Wed, 05 Jun 2013 14:27:28 GMT

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

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

valerio_fatatis — Wed, 05 Jun 2013 14:38:15 GMT

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

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

Anonymous — Wed, 05 Jun 2013 15:11:42 GMT

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

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

valerio_fatatis — Thu, 06 Jun 2013 16:04:29 GMT

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