Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Removing Parts of Value Strings Using Functions/Mapping

I have data from a CSV file that has values in a specific field like this:

Apple 9.29.15

Carrot 9.28.15

Banana 10.11.14

Orange 8.28.15

Apple 9.28.15

Carrot 9.27.15

Ideally, I would like the values to read like this:

Apple

Carrot

Banana

Orange

Apple

Carrot

Since the string values aren't the same character length from the left or the right, I've had a hard time using LEFT or RIGHT functions to snip out these number values.

I also considered using a mapping function to essentially treat values like "Apple 9.29.15" and "Apple 9.28.15" as the same, but it seems to entail actually going through and manually mapping out all instances. This would not be practical with the scale of my data. It also appears that it's not as easy as mapping "Apple" to "Apple*" -- unless there's an easy shortcut to mapping with wildcards.

Does anyone have a suggestion how I can either clean or map my data to appear as above?

6 Replies
lironbaram
Partner - Master III
Partner - Master III

yes

use this function

left(yourfield,index(yourfield,' ')-1)

jonathandienst
Partner - Champion III
Partner - Champion III

The Left() expression above will split on a space. Another way is

=purgechar(yourfield, ' .1234567890')

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Hello,

Ideally this solves your problem.

subfield([Your Field],' ',1)

BR

Serhan

Not applicable
Author

I like the idea of using the purgechar expression to remove the numbers after the value. I don't think splitting based on spaces will work because my data looks more like this:

Banana Fruit Salad 10.14.15

Orange Sherbet 11.22.15

Apple Crisp 11.15.14

Pineapple Upside Down Cake 10.14.14

Not applicable
Author

There are also values that look like this:

2nd Best Pineapple Cake 10.12.14

1st Place Orange Sherbet 10.11.14

So purging number characters is a no go

Not applicable
Author

What would be the best way via function to search the string for the first number value following a space and then deleting everything to the right of that number (including the number)? That way you could have:

Banana Fruit Salad 10.14.15

Orange Sherbet 11.22.15

Apple Crisp 11.15.14

Pineapple Upside Down Cake 10.14.14

2nd Best Pineapple Cake 10.12.14

1st Place Orange Sherbet 10.11.14

Result in:

Banana Fruit Salad

Orange Sherbet

Apple Crisp

Pineapple Upside Down Cake

2nd Best Pineapple Cake

1st Place Orange Sherbet