how to correct this formula?

system · September 8, 2009, 1:35pm

Hello,
I tried to create a Vaiable in WEBI and samething as object in Universe I am getting an Error.For both variable and object formula’s and Error are given Below.Thanks in Advance.

Variable:

=IF([ Code])= “TWT”;“TWT”;
if([Code]) ="";“BLS”;
if ([Code])="S"or ([lvl])=“S”;“SCT”;
if ([Code])= “A”;“ALS” + ([lvl])
if ([BLS]) = “T”;“TWT”
if ([BLS]) = “R”; “AIR”;))))))

Error :- Missing ‘,’ Before Argument in function ’ If’ at Position 19.Error WIS10062.

In Universe Object:

CASE
When(MST.Code)=“TWT” THEN “TWT”
(MST.pLS) = “” then “BLS”
(MST.pLS) = “S” or (MST.LVL) = “S” then “SCT”
(MST.pLS) = “A” then “ALS” || (MST.LVL)
(MST.pLS) = “T” then “TWT”
(MST.pLS) = “R” then “AIR”
END

Error:- Parse failed:Exception:DBD-[IBM] Iseries Access ODBC Driver][DB2 UDB] SQL 0104-token= was not valid token:END State:37000

POOJA

ktm (BOB member since 2008-11-12)

system · September 8, 2009, 1:44pm

Try

=IF([ Code]= “TWT”;“TWT”;
if([Code] ="";“BLS”;
if([Code]="S"or [lvl]=“S”;“SCT”;
if([code]= “A”;“ALS” + [lvl];
if([BLS] = “T”;“TWT”;
if([BLS] = “R”; “AIR” ))))))

Damocles (BOB member since 2006-10-05)

system · September 8, 2009, 1:58pm

Or :-

CASE
When MST.Code='TWT' THEN 'TWT'
When MST.pLS = '' then 'BLS'
When MST.pLS = 'S' or MST.LVL = 'S' then 'SCT'
When MST.pLS = 'A' then 'ALS' || MST.LVL
When MST.pLS = 'T' then 'TWT'
When MST.pLS = 'R' then 'AIR'
END

Although, unsure of the two pipes method of concatenation, I’ve never had to work with DB2…

Mak 1 (BOB member since 2005-01-06)

system · September 8, 2009, 2:13pm

I can Parse the report Variable but it is taking very long time to Run the objects into query panel so we decided to create an object. I used the Case Statement and still I am getting the same Error.
CASE
When MST.Code=‘TWT’ THEN ‘TWT’
When MST.pLS = ‘’ then ‘BLS’
When MST.pLS = ‘S’ or MST.LVL = ‘S’ then ‘SCT’
When MST.pLS = ‘A’ then ‘ALS’ || MST.LVL
When MST.pLS = ‘T’ then ‘TWT’
When MST.pLS = ‘R’ then ‘AIR’
END

Note:- MST.Code, MST.pLS and MST.LVL are Three Objects.

Thank You.
Pooja

ktm (BOB member since 2008-11-12)

system · September 8, 2009, 2:20pm

That formulae is not going to take that long to evaluate once the query has run…

As for the Case statement, well build it up slowly.

I hope you mean database columns, if not replace with the column names, you cannot reference objects in a Case statement this way.

Also, if they are objects, it is not wise to have special characters, such as “.” in the names, it may cause you excel export problems later on.

Does this work:-

CASE
When MST.Code='TWT' THEN 'TWT' END

If so, build each branch and test like that.

Your syntax was completely wrong, a quick glance at a DB2 manual even showed me that ;).

Mak 1 (BOB member since 2005-01-06)

system · September 8, 2009, 2:24pm

For Variable formula , it is Displating Only AIR. Can we Add Trim in Formula Also.

Like If = Trim…

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · September 8, 2009, 2:35pm

Yes, you can, so is the data dirty?

Look at the substring function, you also have one in SQL…

=If(Substring([field];X;Y)....

X and Y values will depend on the position and what you want to display.

You will need to close the bracket after each substring, have a look at the Webi help…

Mak 1 (BOB member since 2005-01-06)

system · September 11, 2009, 12:37pm

I have tried to use Trim In the Below formula it is throwing an Error.Can You Guys share your Thoughts on this.

=IF Trim([ Code]= “TWT”;“TWT”;
if([Code] ="";“BLS”;
if([Code]="S"or [lvl]=“S”;“SCT”;
if([code]= “A”;“ALS” + [lvl];
if([BLS] = “T”;“TWT”;
if([BLS] = “R”; “AIR” )))))

Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · September 11, 2009, 12:55pm

Rather than using Nested If statement , can you use If -Then-Else statement and try…

Thanks

BOCP (BOB member since 2007-07-02)

system · September 11, 2009, 1:18pm

I used the following Formula but it is not Parsing.

IF Trim([Tal] = “TWT” THEN “TWT”
ELSE if[ Code] = “”; Or “B”; then “BLS”;
else if [ Code] = “S” or ([LVL]) = “S” then “SCT”;
else if [Code] = “A” then “ALS” + ([lvl]);
else if [Code] = “T” then “TWT”;
else if [Code] = “R” then “AIR”))))))

Invalid Identifier at TWT

ktm (BOB member since 2008-11-12)

system · September 11, 2009, 1:21pm

Can you remove trim and try??

BTW where are you trying it??

Thanks

BOCP (BOB member since 2007-07-02)

system · September 11, 2009, 2:29pm

I have to use Trim in the Formula.
Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · September 13, 2009, 4:52am

Seems to be problem in using Trim(), check its syntax and try. Take a care when opening and closing parenthesis.

Or

Try without Trim() and see whether it works for you or not??

Right now i have no access to system.

Thanks

BOCP (BOB member since 2007-07-02)

system · September 13, 2009, 11:56pm

I have to use the Triim Function to the Above mentioned Formula and without Trim , I am able to make the Variable.Need to make the Variable using Trim.
Thanks
Pooja

ktm (BOB member since 2008-11-12)

system · September 14, 2009, 6:36am

=IF([ Code]= "TWT";"TWT"; if([Code] ="";"BLS"; if([Code]="S"or [lvl]="S";"SCT"; if([code]= "A";"ALS" + [lvl]; if([BLS] = "T";"TWT"; if([BLS] = "R"; "AIR" ))))))
Your code seems to be confusing. I have some doubts -

What is [|V|]?
What is [BLS]?

Else this variable should not take much time at report level. For an eg.:

=If([Code] = "TWT";"TWT";If(IsNull([Code]);"BLS";If([code] = "S";"SCT")))

Or if possible can you redefine your requirement?

aniketp (BOB member since 2007-10-05)

system · September 14, 2009, 12:59pm

Hello aniketp,
Thanks for Your Reply. The Below Formula is Working fine but i could not get the Space things working in my Reports.I have numbers of Clients which has space but it is not working.I have to show if Code has Spcae or “B” than it is BLS.

IF([Code]= “TWT”;“TWT”;
if([Code] ="";“BLS”;Or([ALS/BLS Code])= “B”;“BLS”
if([Code]="S"Or [lvl]=“S”;“SCT”;
if([code]= “A”;“ALS” + [lvl];
if([LS] = “T”;“TWT”;
if([LS] = “R”; “AIR” ))))))

Thank you Again.
Pooja

ktm (BOB member since 2008-11-12)

system · September 14, 2009, 1:05pm

Pooja, to check for a space you have to use [Code] =" " and not [Code] =""

Jansi (BOB member since 2008-05-12)

system · September 14, 2009, 1:29pm

Hello Jansi,

Thank You Very Much, It works Out.Thank You Again.

Pooja

ktm (BOB member since 2008-11-12)