topic Re: Parsing Data in QlikView

Parsing Data

Wed, 04 Feb 2015 19:34:32 GMT

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

Re: Parsing Data

rwunderlich — Wed, 04 Feb 2015 20:15:17 GMT

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

Re: Parsing Data

Thu, 05 Feb 2015 15:58:46 GMT

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

Re: Parsing Data

MarcoWedel — Thu, 05 Feb 2015 17:22:06 GMT

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;

Re: Parsing Data

Anonymous — Thu, 05 Feb 2015 19:11:26 GMT

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 ...

Re: Parsing Data

Thu, 05 Feb 2015 19:57:57 GMT

Sorry about that. Didnt mean to mess things up.