topic Re: Subfield in QlikView

Subfield

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Subfield

Chanty4u — Thu, 10 Nov 2016 10:16:30 GMT

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;

Re: Subfield

sunny_talwar — Thu, 10 Nov 2016 10:16:59 GMT

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

Re: Subfield

Anonymous — Thu, 10 Nov 2016 10:17:41 GMT

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

Re: Subfield

Thu, 10 Nov 2016 10:25:23 GMT

This is a CSV file

Re: Subfield

sunny_talwar — Thu, 10 Nov 2016 10:30:20 GMT

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

Table:

LOAD @1,

@2,

@3,

@4,

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?

Re: Subfield

Thu, 10 Nov 2016 12:25:25 GMT

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

Re: Subfield

Thu, 10 Nov 2016 13:33:34 GMT

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

Re: Subfield

MarcoWedel — Fri, 11 Nov 2016 00:07:36 GMT

how did you solve the different orders issue?

Re: Subfield

Sun, 13 Nov 2016 07:46:28 GMT

Hi Marco

I have not solved it yet