topic Re: Need Substring Help in QlikView

Need Substring Help

pnn44794 — Sun, 13 Oct 2019 00:19:47 GMT

Hello:

I have a column.

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

Re: Need Substring Help

sunny_talwar — Wed, 22 Nov 2017 20:30:42 GMT

May be this

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

Re: Need Substring Help

sunny_talwar — Wed, 22 Nov 2017 20:31:24 GMT

qvw sample attached

Re: Need Substring Help

pnn44794 — Wed, 22 Nov 2017 20:42:38 GMT

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.

Re: Need Substring Help

pnn44794 — Wed, 22 Nov 2017 21:25:24 GMT

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.

Re: Need Substring Help

sunny_talwar — Thu, 23 Nov 2017 23:28:41 GMT

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

Re: Need Substring Help

pnn44794 — Sun, 26 Nov 2017 21:38:40 GMT

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.

Re: Need Substring Help

sunny_talwar — Mon, 27 Nov 2017 01:24:13 GMT

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

Re: Need Substring Help

pnn44794 — Mon, 04 Dec 2017 16:12:09 GMT

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.