topic Re: how to execute subfield() function in qlikview? in QlikView

how to execute subfield() function in qlikview?

Fri, 02 May 2014 09:09:18 GMT

hi guys,

I create a table box with sub field function. In script page I use a sub field () function after I reload this script but It did not execute perfectly,it shows error, i do not know how to overcome this problem. please tell me out how to solve this problem. I attached snipping file below this box.

Regards:

madhu

Re: how to execute subfield() function in qlikview?

tresB — Fri, 02 May 2014 09:12:43 GMT

Change 'skills' to 'Skills'. // S - in capital.

Re: how to execute subfield() function in qlikview?

swuehl — Fri, 02 May 2014 09:12:47 GMT

First, table names are case sensitive, so your resident load should be

...

Resident employskills;

Then:

Field names are also case sensitive and emp does not exist in table employskills.

Re: how to execute subfield() function in qlikview?

sushil353 — Fri, 02 May 2014 09:13:26 GMT

Hi,

What is the error you are getting?

Skills not skills

also check your first field name it is employeeno and you have taken emp..

HTH

Sushil

Re: how to execute subfield() function in qlikview?

Peter_Cammaert — Fri, 02 May 2014 09:13:50 GMT

You are loading a field "emp" that is not available in your source table. And QlikView is case sensitive, meaning that you have to spell all fieldnames exactly like they were defined. Field "Skills" is available, but "skills" isn't.

Best,

Peter

Re: how to execute subfield() function in qlikview?

its_anandrjs — Fri, 02 May 2014 09:15:11 GMT

Write like

Subfield(Skills,',',1 ) put location of the ',' or share the sample.

Subfield(Skills,',')

Re: how to execute subfield() function in qlikview?

Fri, 02 May 2014 09:18:51 GMT

in the below table script, Field name and table name must be same as above .

Use employeeno instead of emp,

Skills instead of skills,

and emloyskills instead of Employskills.

emloyskills:

Load employeeno,

Skills

FROM
[...\Data\Employskills.xlsx]
(ooxml, embedded labels, table is Sheet1);

indskills:

Load
employeeno, SubField(Skills,',') as Skills

Resident emloyskills;

Drop Table emloyskills;

Re: how to execute subfield() function in qlikview?

its_anandrjs — Fri, 02 May 2014 09:19:02 GMT

You have to write like

employskills:

Load employeeno as emp,

Skills

From Location;

indskills:

Load

emp,Subfield(Skills,',') as skills

Resident employskiils;

Drop table employskills

Re: how to execute subfield() function in qlikview?

Fri, 02 May 2014 09:30:02 GMT

hi chouhan see my script.

EMPLOYSKILLS:

LOAD EMPLOYEENO,

SKILLS

FROM

[..\DATA\EMPLOYSKILLS.XLSX]

(OOXML, EMBEDDED LABELS, TABLE IS SHEET1);

INDSKILLS:

LOAD

[EMPLOYEENO],SUBFIELD('SKILLS',',')AS SKILLS

RESIDENT EMPLOYSKILLS;

Re: how to execute subfield() function in qlikview?

SunilChauhan — Fri, 02 May 2014 09:30:39 GMT

Please give space between )as and write 'Skill' without single quote in your script

i mean to say subfiled(Skills,',') as skills

see the example below if data is like below

abc,123,cde

here subfield('abc,123,cde,',',1) will result in abc

subfield('abc,123,cde',',',2) will Result in 123

subfield('abc,123,cde',',',3) will result in cde

hope this helps

Re: how to execute subfield() function in qlikview?

its_anandrjs — Fri, 02 May 2014 09:32:18 GMT

Remove single cots from the subfield like

in place of this

SUBFIELD('SKILLS',',')AS SKILLS

Write like

SUBFIELD(SKILLS,',')AS SKILLS

Or suggest you check the position of the ',' in the SKILLS field

Re: how to execute subfield() function in qlikview?

Fri, 02 May 2014 09:39:42 GMT

Hi Madhu,

Your approach toward the problem is right but you just need some correction in your code.

They are as follows:

the field name emp in the resident load does not exist in the main table.So either change in any one of the table and make both name same.

and one morething make sure your skills column contains the data like ','.So that you will be able able to view the result.

Just make all the required changes.

Hope it will work.Thanks

Regards,

Biren Agrawalla

Re: how to execute subfield() function in qlikview?

Fri, 02 May 2014 10:09:38 GMT

thanks Srivastava it works but It gives results add old data like this.1.table is excel data 2.is using subfield()function table. I want to results like 3 table, table name { Individual skills table.}

1.old data table:

employeeno	Skills
159	Economics,pharmacology
163	Marketing,sociology,spanish
174	Bookkeeping,particle,Physics
210	Economics,Marketing
215	Economics,Law,Marketing
279	Law,Marketing,German
286	Finance,Marketing,French,German
300	Bookkeeping,Finance

2.after subfield()function applied display table like this:

employeeno	Skills
159	Economics
159	Economics,pharmacology
159	pharmacology
163	Marketing
163	Marketing,sociology,spanish
163	sociology
163	spanish
174	Bookkeeping
174	Bookkeeping,particle,Physics
174	particle
174	Physics
210	Economics
210	Economics,Marketing
210	Marketing
215	Economics
215	Economics,Law,Marketing
215	Law
215	Marketing
279	German
279	Law
279	Law,Marketing,German
279	Marketing
286	Finance
286	Finance,Marketing,French,German
286	French
286	German
286	Marketing
300	Bookkeeping
300	Bookkeeping,Finance
300	Finance

3.perfect table.

Re: how to execute subfield() function in qlikview?

Fri, 02 May 2014 10:15:15 GMT

thanks Agrawalla

Re: how to execute subfield() function in qlikview?

swuehl — Fri, 02 May 2014 10:17:28 GMT

I think your second load will just concatenate to your original table.

Use a NONCONCATENATE LOAD prefix to your second table, then drop the first table at the end of your second load.

Re: how to execute subfield() function in qlikview?

Fri, 02 May 2014 10:19:46 GMT

I Wrote drop table employ skills after i reload script. it displayed empty table .when remove the drop table It reloads then it gives wrong results.

Re: how to execute subfield() function in qlikview?

swuehl — Fri, 02 May 2014 10:21:32 GMT

emloyskills:

Load employeeno,

Skills

FROM
[...\Data\Employskills.xlsx]
(ooxml, embedded labels, table is Sheet1);

indskills:

NOCONCATENATE Load
employeeno, SubField(Skills,',') as Skills

Resident emloyskills;

Drop Table emloyskills;

Re: how to execute subfield() function in qlikview?

nizamsha — Fri, 02 May 2014 10:22:01 GMT

EmployeeSkills:

Load

EmployeeNo ,

Skills

from your Table;

MindSkills:

noconcatenate

load

EmployeeNo as emp,

Subfield(Skills,',',-1) as Skills

resident EmployeeSkills;

Re: how to execute subfield() function in qlikview?

Fri, 02 May 2014 10:24:46 GMT

thanks tressco.

Re: how to execute subfield() function in qlikview?

Fri, 02 May 2014 10:25:39 GMT

i use noconcatenate but it gives same results.