Converting text color condition in the script

[ajsjoshua]

Hi Everyone,

I have a field called Expression in my excel. The color Red() means defect and Green() means passed. I am loading that field using a variable in the qlikview script. Now i would like to calculate how may records are defects and how many has passed.

Expression:

IF(len(trim([Group]))>0,
If(Match([Flow],390)>0 and WildMatch([Group],'ZF40','ZR40','ZU40')>0,Green(),
If(Match([Flow],420)>0 and WildMatch([Group],'ZF40','ZR40')>0,Green(),
If(Match([Flow],400)>0 and Match([Group],'ZCPR')>0,Green(),
If(Match([Flow],430)>0 and Match([Group],'ZCPL')>0,Green(),
If(Match([Flow],440)>0 and WildMatch([Group],'ZF42','ZR42')>0,Green(),
If(Match([Flow],450)>0 and WildMatch([Group],'ZF49','ZR49')>0,Green(),
If(Match([Flow],460)>0 and WildMatch([Group],'ZF42','ZR42')>0,Green(),
If(Match([Flow],470)>0 and WildMatch([Group],'ZF40','ZR40','ZU40')>0,Green(),
If(Match([Flow],410)>0 and WildMatch([Group],'ZF40','ZR40','ZU40')>0,Green(),
Red()
))))))))))

[edwin]

using the same expression, you can create a new field for Pass/Fail:

IF(len(trim([Group]))>0,
If(Match([Flow],390)>0 and WildMatch([Group],'ZF40','ZR40','ZU40')>0,'PASS',
...
If(Match([Flow],410)>0 and WildMatch([Group],'ZF40','ZR40','ZU40')>0,'PASS',
'FAIL'
)))))))))) as NewPassFailField,

[ajsjoshua]

Hi Edwin,

 Thanks for your reply. It should be dynamic. The formula in the excel can be changed by the user at any time. Our script has to calculate dynamically.

Regards,

Joshua.

[edwin]

i dont understand  are you saying the Expression itself is in XLS?  i dont think XLS has a green() function. but im not an XLS expert.maybe if you attach samples that will clear it up as you are making us guess a lot