Splitting the data out of a cell in a spreadsheet

[haymarketpaul]

New to QView

I have an xls spreadsheet with 3 columns

Forename Surname Demographics

Paul Smith MFNJOBFUNCTION=06,MFNSPECIALISTAREAS=08 | 09 | 12,MFNINDUSTRYSECTOR=02

In the 3rd column i need to split the comma seperated values out (and within those any pipe seperated values). So i would get...

JOBFUNCTION 06

SPECIALISTAREAS 08

SPECIALISTAREAS 09

SPECIALISTAREAS 12

INDUSTRYSECTOR 02

I can get the Job Function part with this as there is always only one job function per person

Right(SubField(Demographics,',',1),2) as JobFunctionNumber,
Mid(SubField(Demographics,'=',1),4) as Q1 //Job Function Description

I am not sure how to get the MFNSPECIALISTAREAS out though as each person can have many, and if so, they are seperated by pipe delimiters

Any ideas would help me greatly as i'm quite new to QView

I posted a similar forum post here but i think i over complicated what i wanted to do

http://community.qlik.com/forums/t/44919.aspx

[matt_crowther]

Paul,

You could try looking at the 'TextBetween()' (Search in the Help File) function to split out the middle set of data.

Also the 'FindOneOf()' function will be of use in your left / right extraction.

Hope that proves of use,

Matt - Visual Analytics Ltd

[haymarketpaul · Accepted solution]

Managed to use the SubField function in the end...

//split up the main sections (comma-separated) for ease of understanding!

DemCodes_Temp:

LOAD

    CustomerID,

    SubField(JournoDemoCodes,',') AS Dimension_and_Values

RESIDENT Customer;

//split main sections into dimensions and values

DemCodes_Final:

LOAD

    CustomerID,

    SubField(Dimension_and_Values,'=',1)                        AS    DemoDesc,

    Trim(SubField(SubField(Dimension_and_Values,'=',2),'|'))    AS    Value

RESIDENT DemCodes_Temp;