Parsing Data

[#1]

I am working with a data set which I am bringing in from Excel.  In this data set I have an "Action Item #" field and an "Assign To" field.  The issue I am having is that there are many times when action items are assigned when there are multiple users they are separated by a "/" character.  I want to parse that out of the data and assign in Qlikview the action item to both individuals.  Can anyone help me figure this out?

Example:

Excel Representation

Action Item Number  |  Assignment

001                          |  Nick

002                          |  Nick/Bob/Joe

003                          |  Bob

004                          |  Nick/Joe

How I want Qlik to understand the data:

Action Item Number  |  Assignment

001                          |  Nick

002                          |  Nick

002                          |  Bob

002                          |  Joe

003                          |  Bob

004                          |  Nick

004                          |  Joe

This way when I click on Joe I will get the quantity of 2 and Nick I will get three.

Thanks

~Norman Rea

[rwunderlich · Accepted solution]

In your load statement, use

subfield(Assignment,'/') as Assignment

That will generate a row for each name value.

-Rob

http://masterssummit.com

http://robwunderlich.com

[#3]

That worked perfect!  Here is Part 2 of my question.  What if after I parse each of the items in the load I want to change their values based on a condition.  The data is acronyms and I want to spell them out.

~Norm

[MarcoWedel]

mapAcronyms:

Mapping LOAD * Inline [

Acronym, FullName

ABC, ABCDEFGHIJKLMNOP

BCD, BCDEFGHIJKLMNOPQ

];

table1:

LOAD *,

          ApplyMap('mapAcronyms',Assignment) as FullAssignment;

LOAD [Action Item Number],

         SubField(Assignment,'/') as Assignment

FROM YourSource;

[Anonymous]

Norm,

Now you have two correct answers, but you can mark only one as correct, which is not fair.  It would be much better if you had two separate posts for your two questions, especially when the second question is not a clarification to the first one ...

[#6]

Sorry about that.  Didnt mean to mess things up.