Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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
Honored Contributor II

Re: Removing Parts of Value Strings Using Functions/Mapping

yes

use this function

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

MVP
MVP

Re: Removing Parts of Value Strings Using Functions/Mapping

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

=purgechar(yourfield, ' .1234567890')

darkhorse
Valued Contributor

Re: Removing Parts of Value Strings Using Functions/Mapping

Hello,

Ideally this solves your problem.

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

BR

Serhan

Not applicable

Re: Removing Parts of Value Strings Using Functions/Mapping

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

Re: Removing Parts of Value Strings Using Functions/Mapping

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

Re: Removing Parts of Value Strings Using Functions/Mapping

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