topic If char help in QlikView

If char help

Mon, 14 May 2012 12:55:06 GMT

in excel sheet in a column [Ticked_Id] i have 5 different type od data

1) 0

2) C /CNL FEE

3) F16071

4) F16071/0000000241

5) FF16071 /0000000141 24A20

first three (1,2,3 ) wont change

but at last two (4,5) i want the code after "/" as 10 character (0000000241)

at (4) "/" is 7th character; at (5) "/" is 9th character after space.

how i can write this in is statement.

thanks

If char help

Jason_Michaelides — Mon, 14 May 2012 13:02:12 GMT

Text(SubField(Ticked_Id,'/',2)) should do it.

If char help

Mon, 14 May 2012 13:16:32 GMT

thanks Jason it is helpful

but this time i cant see (1,2,3) and also isee (5) as 0000000141 24A20

but i wanna see (5) as 0000000141

If char help

Jason_Michaelides — Mon, 14 May 2012 13:22:15 GMT

Oops - my bad. No problem - but first let me check your required output:

1) 0

2) CNL

3) F16071

4) 0000000241

5) 0000000141

Is this right? Note that 2) HAS changed as it contains a / and a space.

Jason

Re: If char help

Mon, 14 May 2012 13:29:01 GMT

1) 0

2) CNL

3) F16071

4) 0000000241

5) 0000000141

yes this is the correct output that i want

thanks a lot

If char help

Jason_Michaelides — Mon, 14 May 2012 13:42:36 GMT

In that case try:

Text(Mid(YourField,RangeMax(Index(YourField,'/'),1),IF(Index(YourField,' ')<>0,Index(YourField,' ')-Index(YourField,'/')+1)))

(might have missed a parethesis in there somewhere...can't test it right now!)

Re: If char help

Mon, 14 May 2012 13:53:12 GMT

thanks but it turns output only

FF16071

If char help

Jason_Michaelides — Mon, 14 May 2012 14:08:49 GMT

Hmm - maybe:

Text(Mid(YourField,RangeMax(Index(YourField,'/'),1),IF(Index(YourField,' ')>0,Index(YourField,' ')-Index(YourField,'/')+1,Len(YourField))))

Re: If char help

Mon, 14 May 2012 20:40:32 GMT

Jason thank you very much you are very helpful but this time

the output is

/0000000141 but there musnt be /

also i cant see C /CNL FEE as output

i am new to qlikview so it will be difficult for me to find the result

can you please help again.

thanks

If char help

Jason_Michaelides — Mon, 14 May 2012 20:45:09 GMT

Text(Mid(YourField,RangeMax(Index(YourField,'/')+1,1),IF(Index(YourField,' ')>0,Index(YourField,' ')-Index(YourField,'/')+1,Len(YourField))))

The change above should sort out the / at the start. However this expression is designed to get everything between / and a space. This will translate C /CNL FEE to CNL. I warned about this above.

Jason

If char help

Mon, 14 May 2012 20:58:55 GMT

is this what you need?

if(index(value,'/'),right(value,len(value)-index(value,'/')), value) as field

Re: If char help

Tue, 15 May 2012 06:15:17 GMT

sorry you are right my mistake

1) 0

2) C /CNL FEE

3) F16071

4) 0000000241

5) 0000000141

output will be like above.

Re: If char help

Tue, 15 May 2012 06:16:39 GMT

Alejandro thanks but your expresion gives only 3) and 4) as output

If char help

Jason_Michaelides — Tue, 15 May 2012 06:50:30 GMT

And how is QlikView supposed to know that row 2) needs a different rule applying? Can you please state the rule you want to apply to every row - in plain English, not script code? My expression will left the text between a / and a space in any and every string.

Re: If char help

Tue, 15 May 2012 07:23:52 GMT

my input is

1) 0

2) C /CNL FEE

3) F16071

4) F16071/0000000241

5) FF16071 /0000000141 24A20

my output must be

1) 0

2) C /CNL FEE

3) F16071

4) 0000000241

5) 0000000141

1) 2) 3) will stay same

for 4) output must be 10 character after '/'

for 5) output must be 10 character after '/' but there ise a space before '/' in 5)

thnak you very much, i took so much of your time

note: Jason your last statement turns 5) FF16071 /0000000141 24A20 as empty

If char help

Jason_Michaelides — Tue, 15 May 2012 09:10:32 GMT

merakist,

I cannot see a standard rule that can be applied here. You have a slash "/" and space " " in row 2) but want them ignored, however in rows 4) and 5) you want to pick the text between the "/" and " ". How am I or anyone else supposed to know which rule to apply when!?

Or is the difference becasue there is a letter after the / in row 2), not a number as in rows 4) and 5).

Do you see what I'm asking? In order to write you some code to extract what you need you must explain the Plain English rule e.g. "I want everything between the forward slash and the space. If there isn't a slash then start at the beginning and if there isn't a space end at the end."

As Tom Cruise said to Cuba Gooding Jr. "Help me to help you!"

If char help

Tue, 15 May 2012 09:40:14 GMT

ok Jason, i thought that we could use your statement in an if statement.

i changed the data with REPLACE function and my new fromat is like:

F16071/0000000241

FF16071/000000014124A20

now i need to take only 10 characters after '/'. can you help me about this?

my inputs are above and finally i want 0000000241 as output for both of them

If char help

Jason_Michaelides — Tue, 15 May 2012 09:51:07 GMT

So to be clear - the rule you want to apply to all values loaded in this field is "Give me the first 10 characters after the first forward slash". Is that correct?

If char help

Tue, 15 May 2012 09:58:42 GMT

yes, it is now clear. i want exactely this

If char help

Jason_Michaelides — Tue, 15 May 2012 10:01:10 GMT

If that is definitely the rule you want to apply then try:

LEFT(TEXT(SubField(YourField,'/',2)),10)

MID(YourField,Index(YourField,'/'),10)

Jason