topic Re: Capitalize function and acronyms in QlikView

Capitalize function and acronyms

yura_ratu — Thu, 14 May 2015 09:33:06 GMT

Hi Community,

I need to capitalize text from a string. The issue is - there acronyms in the text. I.e. the result of Capitalize function for

'here is ABC acronym'

text will be

'Here Is Abc Acronym'.

I there any smart way to keep acronyms as they are?

Thanks!

Re: Capitalize function and acronyms

stigchel — Thu, 14 May 2015 09:51:10 GMT

You need a way to recognize the acronyms, I suggest you load a list of acronyms, make it a mapping load where from is the capitalized version and the second all capitals then use the mapsubstring function on the capitalized result

MapSubString('Acro',Capitalize(MyField)) as MyField

The MapSubstring function is used to map parts of any expression to a previously loaded mapping table. The mapping is case sensitive and non-iterative and substrings are mapped from left to right. The syntax is:

mapsubstring('mapname', expr)

This function can be used for mapping parts of any expression on a previously loaded mapping table. The mapping is case sensitive and non-recursive. The substrings are mapped from the left to the right. Mapname is the name of a mapping table previously read by a mapping load or a mapping select statement (see Mapping). The name must be enclosed by single straight quotation marks. Expr is the expression whose result should be mapped by substrings.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, <one>

aa, XYZ

x, b ] ;

MapSubstring ('map1', 'A123') returns 'A<one>23'

MapSubstring ('map1', 'baaar') returns 'bXYZar'

MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'

Re: Capitalize function and acronyms

pokassov — Thu, 14 May 2015 10:06:19 GMT

Hello!

Could you see another approach?

t1:

LOAD * Inline [

here is ABC acronym

This is not BI

];

t2:

LOAD f1,

RowNo() as ID,

SubField(f1,' ') as f2

Resident t1;

t3:

NoConcatenate

LOAD f1,

Concat(if(upper(f2)=f2,f2, Capitalize(f2)),' ',ID) as f3

Resident t2

Group By f1;

DROP Table t2;

Re: Capitalize function and acronyms

avinashelite — Thu, 14 May 2015 10:32:07 GMT

use capitalize

Re: Capitalize function and acronyms

sunilkumarqv — Thu, 14 May 2015 10:49:02 GMT

@hi yuriy

Check this in your script

LOAD Capitalize(TextBetween(Data,SubField(Data,' ',0),SubField(Data,' ',4)))

&''&TextBetween(Data,SubField(Data,' ',3),SubField(Data,' ',5)) as Data;

LOAD * Inline

[Data

here is ABC acronym

];

Re: Capitalize function and acronyms

john_s_w_wood — Thu, 14 May 2015 11:02:54 GMT

Im thinking: using concat on subfield with <space> as the delimiter then a test if upper(<subfield>) = <subfield> then leave subfield as is.

Re: Capitalize function and acronyms

anbu1984 — Thu, 14 May 2015 12:15:08 GMT

Load f1,Concat(f1SubstrNew,' ',Order) Group By f1;
Load *,If(f1Substr=Upper(f1Substr),f1Substr,Capitalize(f1Substr)) As f1SubstrNew;
Load *,SubField(f1,' ',IterNo()) As f1Substr,IterNo() As Order While(IterNo() <= SubStringCount(f1,' ')+1);
LOAD * Inline [
f1
here is ABC acronym
This is not BI
];