Counting characters in a String with spaces

[BootCam]

Hi,

I have a field in a table thats coming from SQL server. Now, as an example, lets say i have two value in the field with same string/value except they have different number of spaces.

'    12345'   --> total 9 characters

'      12345' --> 11 characters

I would like to count the number of characters and filter out the one with the 11 characters and keep the one with 9 characters as sort of removing duplicates if you will.

Tried to use the Verbatim variable but somehow couldn't reach to the solution.

Thanks for your help in advance.

BC

[brunobertels]

Hi 

may be this in script 

temp:

load

...,

yourfiled as yourfield

... 

from  

WHERE len(yourfield) >=9 ; 

[BootCam]

Hi, @brunobertels ,

Len function treats both of them (

'    12345'   --> total 9 characters

'      12345' --> 11 characters

)

the same. So it wont work.

Thanks,

BC

[sidhiq91]

@BootCam  May you can try something like below:

In the back end:

NoConcatenate
Temp:
Load Trim(Value) as Value

Inline [
Value
'   12345'
'    12345'

];

Exit Script;

In the front End it would be:

If this resolves your issue, please like and accept it as a solution.

[brunobertels]

hi 

Sorry my bad  ; 

try 

WHERE len(yourfield) <=9 

[marcus_sommer]

In a rather simple case you may apply something like this:

load distinct keepchar(Field, '0123456789') as Field from X;

If spaces are in general a valid char and/or there are further fields which may have different values in regard to the record and preventing the distinct feature you will probably need several steps, like at first loading the field twice - originally and adjusted and as well with a len-count and within a following sorted resident-load checking the current record against the previous ones to detect the record with the smallest len-count in a flag-field and afterwards you could filter the table against this check. 

[BootCam]

Hi Marcus,

to simply put, can i count below in Qlik and say No.1 has 9 characters and No.2 has 11 characters?

1.  '    12345'   --> total 9 characters

2.  '      12345' --> 11 characters

I have many more values like this in my field and I need to filter out the ones with 9 characters and keep only 11 characters value. This way i can get rid of duplicates.

Thanks,

BC

[BootCam]

single quotes('  ' ) doesnt exists with the value but to show the exact amount of spaces i put the single quotes

MyField
'    12345'
'      12345'

......

.......

.......

PS: MyField is coming from SQL server table

[marcus_sommer]

You could just use:

len(YourField) as FieldLength

to get the number of chars.