Discussion Board for collaboration related to Creating Analytics for QlikView.
i have a simple concat expr formula in a text box:
=Concat(ID,',') but return 21000 values (many...)
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
Go to Solution.
Here is the solution ...
thank you anyway
'T' as OT,
'OBJECT' & RowNo() as OBJECTID,
RowNo() as OBJECTIDPOS
$(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)
=concat(OBJECTID & if(Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID)=$(vClass)-1 or Aggr(Rank(Sum(OBJECTIDPOS)*-1),OBJECTID)=Count(TOTAL DISTINCT OBJECTID),');(')
You can use a subfield to split the string up again within a loop and then rebuild it.
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
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...
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 ?
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 " );( "...
NORMAL CONCAT: (1,2,3,4,5,6,7,... 211000) no good! many long...
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?