Webi Variable Question:
Have a field that is a concatenation of values separated by comma. The field values are variable in length. Values are returned from a BI Universe to Webi.
field1, field12, field3, field1234, f5, f67
Using the POS function I can determine the first position for the first comma. How can I determine the field locations of variable length? I want a variable for each data point.
var1= Field1
var2= Field12
var3= Field3 and so on.
Assume use of SUBSTR, POS, LEN, RIGHT, LEFT although in what context?
try with these steps.
Var1=Substr([Object];1;Pos([Object];",")-1)
Var2=Substr(Substr([Object];Length([Var1])+2;Length([Object]));1;Pos(Substr([Object];Length([Var1])+2;Length([Object]));",")-1)
Var3=Substr(Substr([Object];Length([Var1])+2+Length([Var2])+2;Length([Object]));1;Pos(Substr([Object];Length([Var1])+2+Length([Var2])+2;Length([Object]));",")-1)
Var4=Substr(Substr([Object];Length([Var1])+2+Length([Var2])+2+Length([Var3])+2;Length([Object]));1;Pos(Substr([Object];Length([Var1])+2+Length([Var2])+2+Length([Var3])+2;Length([Object]));",")-1)
Var5=Substr(Substr([Object];Length([Var1])+2+Length([Var2])+2+Length([Var3])+2+Length([Var4])+2;Length([Object]));1;Pos(Substr([Object];Length([Var1])+2+Length([Var2])+2+Length([Var3])+2+Length([Var4])+2;Length([Object]));",")-1)
Var6=Substr([Object];Length([Var1])+2+Length([Var2])+2+Length([Var3])+2+Length([Var4])+2+Length([Var5])+2;Length([Object]))
This works very well, Thank You!
Is there a limit to the number of variables that can be used for an object? Var6 is a date/tm field in my data. In the example provided there is no ;1;Pos(Substr([Object]...... with the preceding variables used on this var. When used as described the date fields populate however there are 2 date/tm fields with one more comma which separates them and a space between the data and time. When I add the ;1;Pos(Substr([Object] and so on, an invalid character error is returned at the ;.
Var6=Substr([Object];Length([Var1])+2+Length([Var2])+2+Length([Var3])+2+Length([Var4])+2+Length([Var5])+2;Length([Object]));1;Pos(Substr([Object];Length([Var1])+2+Length([Var2])+2+Length([Var3])+2+Length([Var4])+2+Length([Var5])+2;Length([Object]));",")-1)
if your date time object is date data type then first you have convert in string data type with Formatdate() funciton.
I should have thought of that. Thank you for pointing this out.