topic Re: Trimming a string containing numbers and text in QlikView

Trimming a string containing numbers and text

Wed, 13 Jul 2016 21:37:40 GMT

Hi I have the following data with the current data and expected data. What's the best way to get this output ?

Is it perhaps easier to do this in excel ?

Many thanks

Re: Trimming a string containing numbers and text

swuehl — Wed, 13 Jul 2016 21:41:52 GMT

Try something like

LOAD

Trim(SubField(CURRENT,'-',2)) as EXPECTED

FROM ....;

Re: Trimming a string containing numbers and text

Wed, 13 Jul 2016 21:48:56 GMT

Thanks Stefan. Any ideas how to do this in excel too ?

Re: Trimming a string containing numbers and text

swuehl — Wed, 13 Jul 2016 21:55:27 GMT

Maybe like

https://blog.udemy.com/how-to-split-cells-in-excel/

Re: Trimming a string containing numbers and text

johnw — Wed, 13 Jul 2016 21:56:30 GMT

Maybe this in Excel?

=TRIM(MID(A2,FIND("-",A2)+2,99))

Re: Trimming a string containing numbers and text

Thu, 14 Jul 2016 06:12:06 GMT

Hi Swuehl,

in extension to this i have one more question

CURRENT	EXPECTED OUTPUT	if I enter these current field values in an input box, I need to show those values in a text box like 1-taxes\|2-sales\|5-employees\|4-taxes\|3-sales\|6-employees
1 - taxes	1-taxes
2 - sales	2-sales
5 - employees	5-employees
4taxes	4-taxes
3sales	3-sales
6employees	6-employees

How do i achieve it?

maxgro‌; tresesco‌; stalwar1‌; jagan‌; hic‌

Thank you!

John

Re: Trimming a string containing numbers and text

tamilarasu — Thu, 14 Jul 2016 06:19:11 GMT

You can also try.

=RIGHT(A2, LEN(A2)-FIND("-", A2))

Re: Trimming a string containing numbers and text

tamilarasu — Thu, 14 Jul 2016 06:31:41 GMT

johndawson

Try something like this,

=Replace('$(vVarName)', chr(10),'|')

vVarName -> Variable name which you assigned in Input box.

Hope this helps.

Re: Trimming a string containing numbers and text

Thu, 14 Jul 2016 06:56:23 GMT

Thanks Tamil Nagaraj for your quick response.

here if you look at my expected out put you need to convert

1 - taxes
2 - sales
5 - employees
4taxes
3sales

6employees

to same format like

1-taxes

2-sales

5-employees

4-taxes

3-sales

6-employees

then your expression will work .

Thank you.

John

Re: Trimming a string containing numbers and text

tamilarasu — Thu, 14 Jul 2016 08:41:28 GMT

Hi John,

Sorry. I have some important work to complete. I'll surely check and update after my work.

Re: Trimming a string containing numbers and text

Thu, 14 Jul 2016 08:56:54 GMT

Hi Sid,

U can try this,

=mid(CURRENT,Index(CURRENT,'-')+1)

Thanks