Need Substring Help

[pnn44794]

Hello:

I have a column.

As always, thanks in advance for any and all help.

[sunny_talwar]

May be this

Num(SubField(Trim(Replace(Replace(SubField(Replace(Upper([SKU Name]), 'CPU', 'CORE PROCESSING UNIT'), 'CORE', 1), '-', ''), ',', ' ')), ' ', -1)) as CPU,

[sunny_talwar]

qvw sample attached

[pnn44794]

Hello Sunny:

I hope you're doing well.  Man, you're quick!  Thank you for the quick response.  Give me about 10 - 15 minutes to check this as I'm on a call at the moment.

[pnn44794]

This absolutely works and I will mark it as the Correct answer.  Two questions though.  One, I just realized I have a similar problem with Memory.  Should I open another thread?  It's almost exactly the same challenge.  The Memory column can have the xx - yy entry in the SKU Name column just like for CPU.

Two,  I understand some of the expression you provided, but not all of it.  Would you mind explaining, in layman terms, what the expression is doing?  It doesn't have to be a long explanation.  I'm hoping to learn something. 

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD [WRM Number],

    [Service Ordered],

    [Template or Non Template],

    [Service Tier],

    [SKU Name],

    Num(SubField(Trim(Replace(Replace(SubField(Replace(Upper([SKU Name]), 'CPU', 'CORE PROCESSING UNIT'), 'CORE', 1), '-', ''), ',', ' ')), ' ', -1)) as CPU,

    Num(SubField(Trim(Replace(Replace(SubField(Upper([SKU Name]), 'GB', 1), '-', ''), ',', ' ')), ' ', -1)) as Memory,

    [Service Tier Cleaned],

    BRN,

    [Project Number],

    Milestone,

    [Line Item],

    [Parent Line Item ID]

FROM

[Substring-Sample-Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

[pnn44794]

Hello Sunny:

Just seeing this. I’ve been away. I will check it out this week. I have a lot of catch up to do. In any event, would you mind explaining what this expression is actually doing???? I hope to learn something.

[sunny_talwar]

Don't really know what to explain here... the script is basically trying to manipulate in order to get what you are looking to get... best way to learn is to piece meal it together by starting from the basic and adding one function at a time

1) Table:

LOAD ...

    Upper([SKU Name])

FROM ...;

2) Table:

LOAD ...

    SubField(Upper([SKU Name]), 'GB', 1)

FROM ...;

3) Table:

LOAD ...

    Replace(SubField(Upper([SKU Name]), 'GB', 1), '-', '')

FROM ...;

4) Table:

LOAD ...

    Replace(Replace(SubField(Upper([SKU Name]), 'GB', 1), '-', ''), ',', ' ')

FROM ...;

5) Table:

LOAD ...

    Trim(Replace(Replace(SubField(Upper([SKU Name]), 'GB', 1), '-', ''), ',', ' '))

FROM ...;

6) Table:

LOAD ...

    SubField(Trim(Replace(Replace(SubField(Upper([SKU Name]), 'GB', 1), '-', ''), ',', ' ')), ' ', -1)

FROM ...;

7) Table:

LOAD ...

    Num(SubField(Trim(Replace(Replace(SubField(Upper([SKU Name]), 'GB', 1), '-', ''), ',', ' ')), ' ', -1))

FROM ...;

This is exactly how I built this and I would have no idea how to better explain it...

[pnn44794]

Hello Again  Sunny:

My apologies for the delay in replying to you on this thread.  The above works perfectly for Memory.  So, both solutions given for CPU and Memory work!  I will mark this answer as correct.

Thank you so much for your help.  I appreciate it.