Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Need Substring Help

Hello:

 

I have a column.

 

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

1 Solution

Accepted Solutions
Highlighted

Re: Need Substring Help

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);

Capture.PNG

View solution in original post

8 Replies
Highlighted

Re: Need Substring Help

May be this

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

Highlighted

Re: Need Substring Help

qvw sample attached

Capture.PNG

Highlighted
Partner
Partner

Re: Need Substring Help

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.

Highlighted
Partner
Partner

Re: Need Substring Help

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. 

Highlighted

Re: Need Substring Help

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);

Capture.PNG

View solution in original post

Highlighted
Partner
Partner

Re: Need Substring Help

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.

Highlighted

Re: Need Substring Help

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...

Highlighted
Partner
Partner

Re: Need Substring Help

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.