Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
haymarketpaul
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

1 Solution

Accepted Solutions
haymarketpaul
Creator III
Creator III
Author

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
matt_crowther
Luminary Alumni
Luminary Alumni

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
Creator III
Creator III
Author

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