Subfield

[#1]

Hi All

I have the following data that appears in one field separated by '-'

Data

Sales - John - 001 - CT - Inside Sales

Sales - Peter - 009 - DBN - B2B

Sales - Paul - 025 - CT - B2B

Marketing - Paul - 003 - JHB - Active

Marketing - Atholl - 011 - DBN - Active

Support - Brian - 005 - CT - Hardware

Support - Brett- 015 - PE - Software

I need to create 3 tables from this data (I assume I would use the Subfield function)

Each for Sales, Marketing, Support and relevant data respectively

Regards

[Chanty4u · Accepted solution]

Is this?

aa:

LOAD * INLINE [

    Data

    Sales - John - 001 - CT - Inside Sales

    Sales - Peter - 009 - DBN - B2B

    Sales - Paul - 025 - CT - B2B

    Marketing - Paul - 003 - JHB - Active

    Marketing - Atholl - 011 - DBN - Active

    Support - Brian - 005 - CT - Hardware

    Support - Brett- 015 - PE - Softwar

];

Result:

LOAD *,

SubField(Data,'-',1) as Da,

SubField(Data,'-',2) as Daa,

SubField(Data,'-',3) as Daaa,

SubField(Data,'-',4) as Daaaa,

SubField(Data,'-',5) as Daaaaa

Resident  aa;

[sunny_talwar]

Is this a text or CSV file? and all the three type of departments (Sales, Marketing, Support, etc) are in one file?

[Anonymous]

Try:

D1 being your header field/field name...

Data1:
load SubField(D1,' - ',1) as Dept,
SubField(D1,' - ',2) as Name,
SubField(D1,' - ',3) as No,
SubField(D1,' - ',4) as City,
SubField(D1,' - ',5) as SubDept

[#5]

This is a CSV file

[sunny_talwar]

You can use - as your delimiter when you load the data into QlikView

Table:

LOAD @1,

    @2,

    @3,

    @4,

    @5

FROM

Community_239359.csv

(txt, codepage is 1252, no labels, delimiter is '-', msq);

Now you want the data to sit in three different tables rather than sitting in just one table?

[#7]

Hi plz look into the attachment, if it works for you, it would help others too.

[#8]

Thank you

Is there a way to create a field based on the 1st string

So if the 1st string is Sales, then the 2nd field should be Name, the 3rd field should be EmployeeNo etc

The data comes in different orders from different departments

Each department however will have the same order

See adjusted data below

    Sales - John - CT - 001 - Inside Sales

    Sales - Peter - DBN - 009 - B2B

    Sales - Paul - CT - 025 - B2B

    Marketing - Active - Paul - JHB - 003

    Marketing - Active - Atholl - DBN - 011

    Support - CT - 005 - Brian - Hardware

    Support - PE - 015 - Brett - Software

[MarcoWedel]

how did you solve the different orders issue?

[#10]

Hi Marco

I have not solved it yet