Query Calculation to Assign 13 Different Hourly Values

[BigChris]

That's really helpful. This part

Invalid length parameter passed to the LEFT or SUBSTRING function.

suggests that there's something wrong with the charindex part of the expression. Can you separate this section into a query and see what you get?

Code Select Expand

charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])

[Lynn]

An invalid length would be null, zero or negative I suspect. If your charindex function does not find a '-' then what is returned? Could be null which wouldn't be valid for a substring. Could be zero in which case you subtract one and pass -1 to the substring function.

Break up your expressions into discrete component elements for troubleshooting purposes. This will help you determine if the charindex expression you've defined will satisfy the length portion of the substring for all scenarios of your data. After you get that worked out you can integrate it into the substring function.

[Cognos_Jan2017]

Every "time" entry has a "-".  However, the "Time Undetermined" which they allow has plenty of entries.  It makes sense that the code fails which it finds the first "Time Undetermined" because there is no "-".

It's 0500 USA East Coast time.  At 0900 today, the App developer has its monthly Users Group WebEx.  Can't always get into details during WebEx but I will mention this as something for them to check.

They will have to provide a solution.  It will be educational to see why they designed it this way.

When the solution is found, I will share in this thread.

Thank you for all you have contributed to this, Bob

[Cognos_Jan2017]

Because I posted this in 2 separate topics, this is where I have muddied the water.  My apology again.

I just replied to Lynn in the "Query Calculation ... " thread.

Your wise observation of the charindex scenario when the code can't find a "-" in "Time Undetermined" looks like the culprit.

As I wrote w/ Lynn, the App developer will have to resolve this.  We have too many future crosstabs to write needing Query Calculations resulting in separate columns for business decisions.

Neat probing for answers in situations like this.

Thank you, and Lynn, for your contributions.  WHEN the App developer provides a solution, I will share in this Topic.

[Cognos_Jan2017]

BigChris and Lynn ...

I modified the Case When to specifically look for "Time Undetermined" ... it WORKED !!!
However in the Crosstab columns, it has not populated all possible Hour Intervals, IE, 0000 to 0059, ... , 2300 to 2359.
It DOES write a column for "Time Undetermined".  Now I need to figure out why all possible columns don't display.

Here is the modified code for Data Item "ABC" ...
case
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,17)='Time Undetermined' then 'Time Undetermined'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='0' then '0000 to 0059'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='1' then '0100 to 0159'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='2' then '0200 to 0259'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='3' then '0300 to 0359'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='4' then '0400 to 0459'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='5' then '0500 to 0559'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='6' then '0600 to 0659'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='7' then '0700 to 0759'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='8' then '0800 to 0859'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='9' then '0900 to 0959'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='10' then '1000 to 1059'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='11' then '1100 to 1159'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='12' then '1200 to 1259'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='13' then '1300 to 1359'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='14' then '1400 to 1459'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='15' then '1500 to 1559'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='16' then '1600 to 1659'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='17' then '1700 to 1759'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='18' then '1800 to 1859'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='19' then '1900 to 1959'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='20' then '2000 to 2059'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='21' then '2100 to 2159'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='22' then '2200 to 2259'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='23' then '2300 to 2359'
END

New issue.  Because I'm a Cognos Novice, I want to write another Query Calculation
for sorting the Query Calc "ABC" in the Crosstab columns, IE ...
Obviously referring to the previous Query Calc of "ABC" doesn't work (please see code
directly below) ... How do I Case When to refer to "ABC"?

case
when ABC ='Time Undetermined' then 1
when ABC ='0000 to 0059' then 2
when ABC ='0100 to 0159' then 3
when ABC ='0200 to 0259' then 4
when ABC ='0300 to 0359' then 5
when ABC ='0400 to 0459' then 6
when ABC ='0500 to 0559' then 7
when ABC ='0600 to 0659' then 8
when ABC ='0700 to 0759' then 9
when ABC ='0800 to 0859' then 10
when ABC ='0900 to 0959' then 11
when ABC ='1000 to 1059' then 12
when ABC ='1100 to 1159' then 13
when ABC ='1200 to 1259' then 14
when ABC ='1300 to 1359' then 15
when ABC ='1400 to 1459' then 16
when ABC ='1500 to 1559' then 17
when ABC ='1600 to 1659' then 18
when ABC ='1700 to 1759' then 19
when ABC ='1800 to 1859' then 20
when ABC ='1900 to 1959' then 21
when ABC ='2000 to 2059' then 22
when ABC ='2100 to 2159' then 23
when ABC ='2200 to 2259' then 24
when ABC ='2300 to 2359' then 25
END

TIA - Bob

[BigChris]

For the first part, it'll only produce a column if there's a matching chunk of data in your dataset. So for example, if you don't have any records with a time between 04:00 and 05:00 then you won't get a column for '0400 to 0459'

For the second part, have you tried just sorting on your new ABC field? If the problem is that you're getting 'Time Undetermined' at the bottom of your list then you could change the description to ' Time Undetermined'

[Cognos_Jan2017]

BigChris - All is OK on the columns appearing.

Plus, the Query Calculation also sorts properly. I agree about placing " Time Undetermined" as the first ascending in the sort.

Looks like this Topic has registered a WIN.

Thanks to all you contributed to this, Bob

[MFGF]

Did you create a new thread that is a duplicate of this problem? http://www.cognoise.com/index.php/topic,32658.0.html

Forum etiquette requests that you do not do this since people may spend time answering something that is already answered. Perhaps it is appropriate to request that a moderator join these posts.

Done! :)

MF.