Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Splitting the data out of a cell in a spreadsheet

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Splitting the data out of a cell in a spreadsheet

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;

View solution in original post

2 Replies
Highlighted
Luminary
Luminary

Splitting the data out of a cell in a spreadsheet

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

Highlighted
Creator III
Creator III

Splitting the data out of a cell in a spreadsheet

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;

View solution in original post