topic Re: Help needed with keepchar() in QlikView

Help needed with keepchar()

venkatg6759 — Thu, 03 Apr 2014 13:47:28 GMT

Hello Everyone,

I have the following expression

left(keepchar(if([Research - US Shares ()]&','&[Overall - US Shares ()]&','&[Research - All Products ()]&','&[Overall - All Products ()]<>',,,',[Research - US Shares ()]&','&[Overall - US Shares ()]&','&[Research - All Products ()]&','&[Overall - All Products ()]),'0123456789'),1).

Which gives me results like as shown

I need the highlighted cell to show result as 11

Re: Help needed with keepchar()

sudeepkm — Thu, 03 Apr 2014 13:55:39 GMT

Do you just want to show the highlighted one as without ',' or you want to have it on all the rows?

Can you please explain the logic from which all rows you want to remove the ','?

Re: Help needed with keepchar()

venkatg6759 — Thu, 03 Apr 2014 14:00:08 GMT

I want the result to be shown as 11.

The result should show only numbers that starts first regardless of ,

for the highlighted cell since it has ,,11,.

The value should show 11 which comes after ,

Re: Help needed with keepchar()

venkatg6759 — Thu, 03 Apr 2014 16:38:22 GMT

Can anyone help me on the expression

Thanks,

Venkata

Re: Help needed with keepchar()

sudeepkm — Fri, 04 Apr 2014 10:12:58 GMT

"The result should show only numbers that starts first regardless of ,"

Do you mean to say that if you just have a single number in the cell then it should be display as number instead of the comma and number combination.

In that case many of other cell should also display the single number where ever the logic satisfy. Please clarify.

Re: Help needed with keepchar()

nizamsha — Fri, 04 Apr 2014 10:31:32 GMT

KeepChar( NewChar,'0123456789')

can u post some sample qvd so that we i can do

Re: Re: Help needed with keepchar()

venkatg6759 — Fri, 04 Apr 2014 13:40:20 GMT

Find the sample QVD in the attachment.

The result should show

EMEA & Asia logic	US logic
3	3
4	4
2	2
3	3
1	1
1	1
2	2
4	4
1	1
7	7
4	4
3	3
8	8
2	2
2	2
3	3
1	1
1	1
3	3
2	2
6	6
8	8
1	1
4	4
1	1
5	5
11	11

If there is two digits in it something like ,,11.It should show 11.

Re: Re: Re: Help needed with keepchar()

venkatg6759 — Fri, 11 Apr 2014 18:23:26 GMT

Can anyone help me with the expression.

Check the attached qvw and help me to get the result like

Logic EMEA & Asia Logic

,,,7 7

,11,,, 11

11 should come as result if logic is 11 .

Top 10 should appear as 10 .

Thanks.

Venkata

Re: Re: Re: Re: Help needed with keepchar()

Fri, 11 Apr 2014 19:56:11 GMT

Try something like this. With so many IF statements you would want to be very wary of performance issues, but something like this may work if you put it in the script or don't have much data.

(Replace(if(IsNull(if(SubField([US logic],',',1)<>'',SubField([US logic],',',1)))=0,if(SubField([US logic],',',1)<>'',SubField([US logic],',',1)),
if(IsNull(if(SubField([US logic],',',1)<>'',SubField([US logic],',',1))) and IsNull(if(SubField([US logic],',',2)<>'',SubField([US logic],',',2)))=0,if(SubField([US logic],',',2)<>'',SubField([US logic],',',2)),
if(IsNull(if(SubField([US logic],',',1)<>'',SubField([US logic],',',1))) and IsNull(if(SubField([US logic],',',2)<>'',SubField([US logic],',',2))) and IsNull(if(SubField([US logic],',',3)<>'',SubField([US logic],',',3)))=0,if(SubField([US logic],',',3)<>'',SubField([US logic],',',3)),
if(IsNull(if(SubField([US logic],',',1)<>'',SubField([US logic],',',1))) and IsNull(if(SubField([US logic],',',2)<>'',SubField([US logic],',',2))) and IsNull(if(SubField([US logic],',',3)<>'',SubField([US logic],',',3))) and IsNull(if(SubField([US logic],',',4)<>'',SubField([US logic],',',4)))=0,if(SubField([US logic],',',4)<>'',SubField([US logic],',',4)))))),'Top ',''),'Tier ','')

Example attached.

Re: Help needed with keepchar()

sebastiandperei — Sat, 12 Apr 2014 03:21:40 GMT

How do you need to show "1,2,3,4", "1 or 2", ",Tier 2,,Tier 2" values?

Re: Help needed with keepchar()

venkatg6759 — Mon, 14 Apr 2014 14:35:07 GMT

if it is 1 or 2 it should be displayed as 1 ,if tier 2 it should be 2 . if top 10 it should be 10.

Re: Help needed with keepchar()

preminqlik — Mon, 14 Apr 2014 15:15:29 GMT

Hi there please find attachement

=if(len(subfield(trim(keepchar(rtrim(ltrim(replace([EMEA & Asia logic],',',' '))),' 0123456789')),' ',1))>0,subfield(trim(keepchar(rtrim(ltrim(replace([EMEA & Asia logic],',',' '))),' 0123456789')),' ',1),

trim(keepchar(rtrim(ltrim(replace([EMEA & Asia logic],',',' '))),' 0123456789')))