Hi
i have created a query that contains 23 data items - all works fine and when viewing tabular data this displays no issues and can view data fine.
when i add the following data item (below) - the validation is fine, no errors, but when viewing tabular data or trying to run report i get an error - Expression Parsing Error - QE-DEF-0030
IF (( [Opportunity ID] contains [LG01~ Description] ) OR
( [Opportunity ID] contains [LG02~ Description] ) OR
( [Opportunity ID] contains [LG03~ Description] ) OR
( [Opportunity ID] contains [LG04~ Description] ) OR
( [Opportunity ID] contains [LG05~ Description] ) OR
( [Opportunity ID] contains [LG06~ Description] ) OR
( [Opportunity ID] contains [LG07~ Description] ) OR
( [Opportunity ID] contains [LG08~ Description] ) OR
( [Opportunity ID] contains [LG09~ Description] ) OR
( [Opportunity ID] contains [LG010~ Description] ) OR
( [Opportunity ID] contains [LG011~ Description] ) OR
( [Opportunity ID] contains [LG012~ Description] ) OR
( [Opportunity ID] contains [HG01~ Description] ) OR
( [Opportunity ID] contains [HG02~ Description] ) OR
( [Opportunity ID] contains [HG03~ Description] ) OR
( [Opportunity ID] contains [HG04~ Description] ) OR
( [Opportunity ID] contains [AA01~ Description] ) OR
( [Opportunity ID] contains [YS01~ Description] ) OR
( [Opportunity ID] contains [NS01~ Description] )) THEN
( 'Clinical Check' )
ELSE
( 'Adding Items' )
can anyone help with what is happening? its driving me mad!
this one *might* apply to the situation...?
http://www-01.ibm.com/support/docview.wss?uid=swg21340011
Hi,
Can an ID contain a description? Try hard coding the description and see if the error still exists. And also post a sample data of ID and the Description.
Good luck
New guy
hespora - i will check this now.
New_Guy
ID - are anything in a 6 digit range(numbers only) and description is text such as 'New item being added on' ...... and 'Clinical Check complete by' so the description field contains text as well as dates - am i right in thinking now there is a conflict with the type of data being used in expression?
Hi,
Yes the data type is the issue.
You have to create it like
if
(([description] contains 'Clinical Check complete by')
or
([description] contains 'Clinical Check started on'))
then
'clinicalcheck'
else
'Adding Items'.
Good luck
New guy
I will test this when i get home tonight and let you know outcome! - thank you for help so far!
ill be in touch!
If I understand your description you're asking for records that match this logic:
[short field] contains [long field]
like this:
'A' contains 'Aardvark'
That will always be false.
So i have played around with the expression and i think it seems correct now but when i run tabular data it gives me wrong data - below is expression
IF ( ([Order Audit Notes] contains 'LG01~') OR
([Order Audit Notes] contains 'LG02~') OR
([Order Audit Notes] contains 'LG03~') OR
([Order Audit Notes] contains 'LG04~') OR
([Order Audit Notes] contains 'LG05~') OR
([Order Audit Notes] contains 'LG06~') OR
([Order Audit Notes] contains 'LG07~') OR
([Order Audit Notes] contains 'LG08~') OR
([Order Audit Notes] contains 'LG09~') OR
([Order Audit Notes] contains 'LG10~') OR
([Order Audit Notes] contains 'LG11~') OR
([Order Audit Notes] contains 'LG12~') OR
([Order Audit Notes] contains 'HG01~') OR
([Order Audit Notes] contains 'HG02~') OR
([Order Audit Notes] contains 'HG03~') OR
([Order Audit Notes] contains 'HG04~') OR
([Order Audit Notes] contains 'YS01~') OR
([Order Audit Notes] contains 'NS01~') OR
([Order Audit Notes] contains 'AA01~')
) THEN
( 'Clinical Check' )
ELSE
( 'Adding Items' )
please see attached for example data with in data items i am using - i am trying to separate each row to specify weather it was a clinical check or weather a new items has been added - unfortunately this is the only place these bits of information are stored and i am needing to produce a report to show how many items added by each user and then how many clinical checks by each user.
current issue is that everything is categorized as adding items from this data item so i am not sure if i am doing something wrong in the above?
hope this makes sense and appreciate any replies!- look forward to your responses!
Quote from: imzy66 on 02 Jun 2017 03:04:50 AM
So i have played around with the expression and i think it seems correct now but when i run tabular data it gives me wrong data - below is expression
IF ( ([Order Audit Notes] contains 'LG01~') OR
([Order Audit Notes] contains 'LG02~') OR
([Order Audit Notes] contains 'LG03~') OR
([Order Audit Notes] contains 'LG04~') OR
([Order Audit Notes] contains 'LG05~') OR
([Order Audit Notes] contains 'LG06~') OR
([Order Audit Notes] contains 'LG07~') OR
([Order Audit Notes] contains 'LG08~') OR
([Order Audit Notes] contains 'LG09~') OR
([Order Audit Notes] contains 'LG10~') OR
([Order Audit Notes] contains 'LG11~') OR
([Order Audit Notes] contains 'LG12~') OR
([Order Audit Notes] contains 'HG01~') OR
([Order Audit Notes] contains 'HG02~') OR
([Order Audit Notes] contains 'HG03~') OR
([Order Audit Notes] contains 'HG04~') OR
([Order Audit Notes] contains 'YS01~') OR
([Order Audit Notes] contains 'NS01~') OR
([Order Audit Notes] contains 'AA01~')
) THEN
( 'Clinical Check' )
ELSE
( 'Adding Items' )
please see attached for example data with in data items i am using - i am trying to separate each row to specify weather it was a clinical check or weather a new items has been added - unfortunately this is the only place these bits of information are stored and i am needing to produce a report to show how many items added by each user and then how many clinical checks by each user.
current issue is that everything is categorized as adding items from this data item so i am not sure if i am doing something wrong in the above?
hope this makes sense and appreciate any replies!- look forward to your responses!
Hi,
I think you need to add the % wildcard character before and after the values, eg:
IF ( ([Order Audit Notes] contains '%LG01~%') OR
([Order Audit Notes] contains '%LG02~%') OR
([Order Audit Notes] contains '%LG03~%') OR
([Order Audit Notes] contains '%LG04~%') OR
([Order Audit Notes] contains '%LG05~%') OR
([Order Audit Notes] contains '%LG06~%') OR
([Order Audit Notes] contains '%LG07~%') OR
([Order Audit Notes] contains '%LG08~%') OR
([Order Audit Notes] contains '%LG09~%') OR
([Order Audit Notes] contains '%LG10~%') OR
([Order Audit Notes] contains '%LG11~%') OR
([Order Audit Notes] contains '%LG12~%') OR
([Order Audit Notes] contains %'HG01~%') OR
([Order Audit Notes] contains %'HG02~%') OR
([Order Audit Notes] contains '%HG03~%') OR
([Order Audit Notes] contains '%HG04~%') OR
([Order Audit Notes] contains '%YS01~%') OR
([Order Audit Notes] contains '%NS01~%') OR
([Order Audit Notes] contains '%AA01~%')
) THEN
( 'Clinical Check' )
ELSE
( 'Adding Items' )
Cheers!
MF.
Ill give this a go in next half hour!
Sent from my SM-G925F using Tapatalk
MF -
i just tried what you suggested, this validates but can not view tabular data - i am getting the following error message : -
QE-DEF-0260
Parsing error before or near position: 576 of: "IF ( ([Order Audit Notes] contains '%LG01~%') OR ([Order Audit Notes] contains '%LG02~%') OR ([Order Audit Notes] contains '%LG03~%') OR ([Order Audit Notes] contains '%LG04~%') OR ([Order Audit Notes] contains '%LG05~%') OR ([Order Audit Notes] contains '%LG06~%') OR ([Order Audit Notes] contains '%LG07~%') OR ([Order Audit Notes] contains '%LG08~%') OR ([Order Audit Notes] contains '%LG09~%') OR ([Order Audit Notes] contains '%LG10~%') OR ([Order Audit Notes] contains '%LG11~%') OR ([Order Audit Notes] contains '%LG12~%') OR ([Order Audit Notes] contains "
Details
DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2017-06-02-12:15:00.941-#250
Quote from: imzy66 on 02 Jun 2017 06:18:33 AM
MF -
i just tried what you suggested, this validates but can not view tabular data - i am getting the following error message : -
QE-DEF-0260
Parsing error before or near position: 576 of: "IF ( ([Order Audit Notes] contains '%LG01~%') OR ([Order Audit Notes] contains '%LG02~%') OR ([Order Audit Notes] contains '%LG03~%') OR ([Order Audit Notes] contains '%LG04~%') OR ([Order Audit Notes] contains '%LG05~%') OR ([Order Audit Notes] contains '%LG06~%') OR ([Order Audit Notes] contains '%LG07~%') OR ([Order Audit Notes] contains '%LG08~%') OR ([Order Audit Notes] contains '%LG09~%') OR ([Order Audit Notes] contains '%LG10~%') OR ([Order Audit Notes] contains '%LG11~%') OR ([Order Audit Notes] contains '%LG12~%') OR ([Order Audit Notes] contains "
Details
DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2017-06-02-12:15:00.941-#250
Oops!
Spot the typo in my previous reply. I put
([Order Audit Notes] contains %'HG01~%') OR
([Order Audit Notes] contains %'HG02~%') OR
and it should of course be
([Order Audit Notes] contains '%HG01~%') OR
([Order Audit Notes] contains '%HG02~%') OR
I guess you copied and pasted from my reply rather than manually editing your expression to add the wildcards? :)
MF.
had a quick glance and looked ok but can now see what you are saying - and yes i did http://www.cognoise.com/Smileys/default/rolleyes.gif
just checked now and i am getting tabular data but still not worked :/ getting same results as last image posted - see attached for results
Hi,
Can you try like and % and check the result?
Good luck
New guy
Quote from: imzy66 on 02 Jun 2017 06:27:28 AM
had a quick glance and looked ok but can now see what you are saying - and yes i did http://www.cognoise.com/Smileys/default/rolleyes.gif
just checked now and i am getting tabular data but still not worked :/ getting same results as last image posted - see attached for results
Hi,
Ok - that makes me a little suspicious that something else is going on. In the image of the rendered report, it looks like the Order Audit Notes column is displaying the text you need to parse on a new line. Are we sure this is actually part of the Order Audit Notes column, or do you have a separate item for this included in the same list column? (Cognos allows you to display multiple data items in the same list column). Can you show an image of the list as it appears in Report Studio?
MF.
it is strange - as i have another report that i have data items which search for just individual codes (i.e LG01~) and then categorizes this into the meaning behind the code for example
if([Modelling Layer].[T Opportunity Order Audit No Orders].[Order Audit Notes] contains ('HG04~')) then ('No prescriber GMC number') else (null)
This works fine in the other report but as soon as i do this in this one it just doesn't work - you think might be worth deleting whole query and starting again?
see attached for current view of data items with in report studio previous post.
Quote from: New_Guy on 02 Jun 2017 07:09:28 AM
Hi,
Can you try like and % and check the result?
Good luck
New guy
still no joy - everything is just classed under Adding items
I prefer case statements although it should work fine either way. The case will stop evaluating once a match is found but that should not be a problem in this case. I didn't put it all the possible choices but you get the idea in case you want to try a different expression.
There may be something else going on as MFGF said since it should work with if/then/else.
CASE
WHEN [Order Audit Notes] LIKE '%LG01~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG02~%' THEN 'Clinical Check'
...
WHEN [Order Audit Notes] LIKE '%AA01~%' THEN 'Clinical Check'
ELSE
'Adding Items'
Case
WHEN [Order Audit Notes] LIKE '%LG01~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG02~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG03~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG04~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG05~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG06~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG07~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG08~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG09~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG10~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG11~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%LG12~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%NS01~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%YS01~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%AA01~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%HG01~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%HG02~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%HG03~%' THEN 'Clinical Check'
WHEN [Order Audit Notes] LIKE '%HG04~%' THEN 'Clinical Check'
ELSE
'Adding Items'
END
Im getting a parsing error when attempting to view tabular data can you see an error above?
i have created a second query that search for each of the codes (LG01~) individually and this works - not sure why or how, see attached for screen shot of this working - but now i have 19 data items that will display 'Clinical Check' when tabular data is run - how would i then tell the report to check if any of these have 'Clinical Check' entered then count for each user id , then the rest to be classed as adding items and then count again for each user id. Hope this makes sense
Thank you for your help so far too!
Just to update you all - form my previous post - i had 19 data items which took into account each code (i.e LG01~) and then classified them form this - i then created another data item with the following expression: -
IF(([LG01~ Description] is not missing) OR
([LG02~ Description] is not missing) OR
([LG03~ Description] is not missing) OR
([LG04~ Description] is not missing) OR
([LG05~ Description] is not missing) OR
([LG06~ Description] is not missing) OR
([LG07~ Description] is not missing) OR
([LG08~ Description] is not missing) OR
([LG09~ Description] is not missing) OR
([LG10~ Description] is not missing) OR
([LG11~ Description] is not missing) OR
([LG12~ Description] is not missing) OR
([HG01~ Description] is not missing) OR
([HG02~ Description] is not missing) OR
([HG03~ Description] is not missing) OR
([HG04~ Description] is not missing) OR
([NS01~ Description] is not missing) OR
([YS01~ Description] is not missing) OR
([AA01~ Description] is not missing))
THEN
('Clinical Check')
ELSE
('Adding Items')
then 2 more data items to then count each users for both adding items and clinical checks!
now checked and finally what i needed!
Thank you for everyone who tried to help!! much appreciated!!