topic Re: removed the single alphabet from the string in QlikView

removed the single alphabet from the string

alis2063 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

How are you?

i want to get rid of single alphabet from the string

exp

NH JU Y OK

TH JU S OK

i have two strings as above so i am expecting the result as below can you please help me out.

NH JU OK

TH JU OK

if you all have observed that i have removed the single alphabet from given strings.

Re: removed the single alphabet from the string

robert_mika — Tue, 22 Nov 2016 16:56:24 GMT

something like this:

(the only downturn is that the order will be slightly changed)

temp:

load

RecNo() as row,

SubField(A,' ') as Field;

load * inline [

'NH JU Y OK'

'TH JU S OK'

];

load Concat( Field,' ') as NewField,

row

Resident temp

where len(Field)>1

Group by row;

drop table temp;

Re: removed the single alphabet from the string

tresB — Tue, 22 Nov 2016 16:59:10 GMT

One way could be like:

Input:

Load *, RowNo() as ID Inline [

String

NH JU Y OK

TH JU S OK

];

Join

Load StrTemp, ID

where Len(StrTemp)=1;

Load SubField(String,' ') as StrTemp, ID Resident Input;

Final:

Load Replace(String, ' '&StrTemp&' ', ' ') as finalString Resident Input;

Drop table Input;

Re: removed the single alphabet from the string

tresB — Tue, 22 Nov 2016 17:00:47 GMT

I too was trying the same - but the string gets jumbled up because of the Sort Order in concat().

Re: removed the single alphabet from the string

swuehl — Tue, 22 Nov 2016 17:19:37 GMT

There is an optional argument for sort weight available for the Concat() function ...

temp:

load

RecNo() as row,

SubField(A,' ', iterno()) as Field,

iterno() as index

inline [

'NH JU Y OK'

'TH JU S OK'

]

WHILE iterno() <= Substringcount(A,' ')+1;

load Concat( Field,' ',index) as NewField,

row

Resident temp

where len(Field)>1

Group by row;

drop table temp;

Re: removed the single alphabet from the string

alis2063 — Tue, 22 Nov 2016 17:47:13 GMT

Hi Stefan,

Thanks for your great effort.

Can you please let me know that is there any possibility can we handle the above scenario through the variable as i did in Sap Business object Tool but its different approach so i thinks that it may be possible on variable level.

Regards,

Ali

Re: removed the single alphabet from the string

alis2063 — Tue, 22 Nov 2016 17:47:43 GMT

Hi Tresco,

Thanks for your great effort.

Regards,

Ali

Re: removed the single alphabet from the string

alis2063 — Tue, 22 Nov 2016 17:48:16 GMT

Hi Robert,

Hope you well.

Thanks for your great effort.

Regards,

Ali

Re: removed the single alphabet from the string

tresB — Wed, 23 Nov 2016 05:26:59 GMT

By 'variable level' if you mean UI solution, I don't see an easy dynamic solution for this.

Re: removed the single alphabet from the string

susovan — Wed, 23 Nov 2016 06:59:41 GMT

Hi Salman,

It might be work,

So, please check the attachment.

Re: removed the single alphabet from the string

alis2063 — Wed, 23 Nov 2016 07:53:44 GMT

Hi Susovan,

Can you please solutions for the same it just attachment .

Re: removed the single alphabet from the string

alis2063 — Wed, 23 Nov 2016 14:48:35 GMT

@Robertmika

Hi Mark,

I have implemented the same as above ,its working as i wanted but when i used it through OLEDB CONNECTION then it is throwing error

1)Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'RecNo' is not a recognized built-in function name

2)Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'SubFeild' is not a recognized built-in function name

Can you please suggest me as i need to implement that on OLEBD Connection.

Re: removed the single alphabet from the string

swuehl — Wed, 23 Nov 2016 14:55:06 GMT

Use a preceding LOAD statement to be able to use QV functions (which are not known to the DBMS driver):

Preceding Load

Re: removed the single alphabet from the string

danansell42 — Wed, 23 Nov 2016 15:30:54 GMT

If its just for presentation purposes on the front end then you could just create a calculated dimension within charts/tables etc.

Obviously this is also dependant on the format being the same in every instance

For example:

=left(Field,6)&right(Field,2)

Re: removed the single alphabet from the string

alis2063 — Wed, 23 Nov 2016 16:09:07 GMT

@STEFABWUHI

@ALIS2063

Hi Stefab,

Thanks for your quick respond.

finally got solution .

Regards,

Ali