How do I change data in a particular column in QV to drop the date out?

[#1]

I am loading a group of reports, adding a new one each day.

In the report there is column named "Skill" that gives along name for one of four skills.

In the daily report, the report appends the date to the skill name like so:

• CSO 8/3/2013
• BAP 8/3/2013
• MRSL 8/3/2013
• OBD 8/3/2013
• CSO 8/4/2013
• BAP 8/4/2013
• MRSL 8/342013
• OBD 8/4/2013

So suddenly, instead of four "Skills" that I can use to sort by, I suddenly have many skills and another set of four each day.

What I want is for every value in this column to be one of these four:

• CSO
• BAP
• MRSL
• OBD

The export of the report to the source-file locatiojn is automated, so I need to make the changes within QV.

I need to drop the dates from the above values preferably in the load scrit.

Any thoughts on this one?

Steve

[Anonymous]

Steve,

There is a space between useful data and the date, so you can use subfield function in QV load script:

subfield(Skills, ' ', 1) as Skills

Regards,

Michael

[#3]

OK, I used that function like so:

SUBFIELD ([Ticketing Handled by Skill by Day for CSO - High Priority],',') AS  [Skill Final]

and got this:

How do I lose the date values and just keep the four skills?

Also, can I use the same approach to pare down the remaining Skill names and pull out the "Ticketing Handled by Skill by Hour for" portion too?

Steve

[Anonymous]

Steve,

It can happen if sometimes the date is the first, not the second.  You can use another function, purgechar():

trim(purgechar([Ticketing Handled by Skill by Day for CSO - High Priority],'0123456789/') AS  [Skill Final]))

It removes characters I mrked in bold.  And, trim() removes leadingand trailing spaces.  To remove that long string, add the replace() function, replace it with nothing (open and close sinfle quote):

replace( trim(purgechar([Ticketing Handled by Skill by Day for CSO - High Priority],'0123456789/')

, 'Ticketing Handled by Skill by Hour for', '') AS  [Skill Final]))

Regards,

Michael

[#5]

Thanks Michael,

I will work with this and see if I can make it work. I can tell you that in my doc, the braketting does not look right and it returns errors.

I will let you know if I get it to work right.

Steve

[Anonymous · Accepted solution]

Yes, I messed up the parenthesis by copy/pasting...  😞

Try this:

replace( trim(purgechar([Ticketing Handled by Skill by Day for CSO - High Priority],'0123456789/'))

, 'Ticketing Handled by Skill by Hour for', '') AS  [Skill Final]

[#7]

That did the trick! This works perfectly and I learned something useful today. 

Thanks a million Michael. i hoe that you have a great day.

Steve