Stringing together IF statements

[csavage92]

Hello!

I am working to create a formula that uses the capitialize argument to help show fields in "proper" format.
We have words combined and need to capitalize the first letter but then also the first letter of the word following.

I have been able to create the formula for only one of the combinations "BIO" but I am struggling on how to create a second layer to this for example "ECO."

The middle of the argument below is what I need to add on top but am not successful, below is the argument that has worked until now:

=Capitalize(lower(ColorLong))&' ' &
if(index(FabricType,'BIO')>0, 'Bio' & Capitalize(lower(subfield(FabricType,'BIO',2)))
,Capitalize(Upper(FabricType)))

I am still getting familiar with the Qlik language so any help is appreciated!

[rwunderlich]

Do you want to generally capitalize each word in the field or only specific words like 'ECO' and "BIO"?

Can this be done in the script or does it have to be done in the UI?

-Rob

[csavage92]

I am looking to have the first letter of the words after BIO and ECO capitalized (along with making sure the B in bio and E in eco are also capitalized).

This is a one off so we were looking to accomplish in the script.

Thanks!!

[Chanty4u]

Try this 

=Capitalize(lower(ColorLong)) & ' ' &

Capitalize(Replace(Replace(FabricType, 'BIO', 'Bio '), 'ECO', 'Eco '))

[rwunderlich]

I'm not understanding why Capitalize(Upper(FabricType) wouldn't work for all cases? Can you post some sample data?

-Rob

[csavage92]

Thank you!

I can't have a space after the Bio or Eco, I tried removing the space at the end of the Bio and Eco but that makes it so the second word that is connected is now lowercase.
I was looking to have BioSculpt be an example output.

[csavage92]

Our FabricType is really two words pushed together. The value is 'BIOSCULPT' but I need it to show as 'BioSculpt'

The original formula works but I need this same logic for a couple more ('ECO' and 'SUPER' need to be added to the argument). I am struggling with how to add more if statements to the original formula.

Thank you for the help!!

[marcus_sommer · Accepted solution]

I think I would use a mapping to split the string into two words, for example like:

m: mapping load *, Lookup & ' ' as Return inline [
Lookup
bio
super
eco
... 
];

t: load *, capitalize(mapsubstring('m', FabricType)) as X from Y;

[csavage92]

thank you!!!