Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
margretand
Contributor
Contributor

Script - subfield

Hi. I have a problem of very basic nature. 

I want to create a script to split a column in a excel-sheet into two columns. I then try with subfield function. But don´t know exactly how to write the code, to load the data and make the script work.

Lets say the excel file name is: hello1

And name of sheet is: Sheet1

And columns in excel sheet is:

Column-headlines: A = company, B = company ID and C = revenue

Company ID values column are then like: Company-12, Company-15. And so on. And I want the ID to be only written as 12, 15 and so on.  I tried with subfield, not sure to place it in already existing script or better to create a new one.

I placed the attempt below (but I get error). Not sure if I need to write or do something more to tell which file it shall take the values from. The file is already loaded into qliksense desktop but needs to be manipulated with the script editior.

Very grateful for help!

LOAD
company,
SubField(CompanyID,'-',1) as text,
Subfield(CompanyID,'-',2) as ID,
revenue,

FROM,


(xls, embedded labels, table is Sheet1);

Exit Script;

Labels (2)
8 Replies
sunny_talwar

This looks good to me... is this not working?

LOAD
company,
SubField(CompanyID,'-',1) as text,
Subfield(CompanyID,'-',2) as ID,
revenue

margretand
Contributor
Contributor
Author

No, I get the following error message..

Unexpected token: 'xls', expected one of: 'xml', 'xmlGeneric', 'xmlSax', 'xmlSimple', 'biff', 'dif', 'fix', ...
 
The error takes place here:
LOAD Company, SubField(CompanyID,'-',1) as text, Subfield(CompanyID,'-',2) as ID, Revenue FROM, (>>>>>>xls<<<<<<, embedded labels, table is Sheet1)
 
If I take away the last part of the script like you did I get:
 
Unexpected token: 'Set', expected one of: ',', ':', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', ...
 
The error takes place here:
LOAD Company, SubField(CompanyID,'-',1) as text, Subfield(CompanyID,'-',2) as ID, Revenue
Vegar
Partner
Partner

This script gave me the output in the picture below:

LOAD
  A as Company,
  SubField(B,'-',1) as CompanyText,
  Subfield(B,'-',2) as CompanyID,
  C as Revenue
Inline [
  A, B, C
  Qlik, Company-12, 612
  Tableau, Company-15, 202
  ThoughtSpot, Company-19, 31

];

 

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
sunny_talwar

Are you forgetting to add the file name?

image.png

I initially thought this was just a pseudo code and you are having the file name in your original code. But it seems you forgot to add that, did you?

Vegar
Partner
Partner


@margretand wrote:

[...]

The error takes place here:
LOAD Company, SubField(CompanyID,'-',1) as text, Subfield(CompanyID,'-',2) as ID, Revenue FROM, (>>>>>>xls<<<<<<, embedded labels, table is Sheet1)
 

I notice two things.

1. a comma (,) after FROM

2. It looks like it is the filename and or the format specification where the error occur. Could you show us a bit more of your script?

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Anil_Babu_Samineni

Is this something typo error? remove highlighted one which is in Red color

LOAD
company,
SubField(CompanyID,'-',1) as text,
Subfield(CompanyID,'-',2) as ID,
revenue,

FROM, // Which table you are loading?

(xls, embedded labels, table is Sheet1);

Note: Please share full script

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
margretand
Contributor
Contributor
Author

Yes, I am sure it is either something with the typo or that I need to specify the file more correct. 

I changed the script to this now (this is the entire script text)

 

LOAD
Company,
SubField(CompanyId,'-',1) as text,
Subfield(CompanyId,'-',2) as ID,
Revenue

FROM hello1(1).xls             

(xml, embedded labels, table is Sheet1);

 

The hello1(1).xls is the file name on the computer. I have uploaded hello1 already to qliksense desktop where I can find the different files in the data-editor. (like 4 different files). Dont know exactly how to specify the last 2 rows. If it should be entire file name (with .xls). If the last row need to be there and why I can not write xls as format there..)

The file I am trying to split one of the columns into two based on the - is just a normal excel file uploaded in qliksense dekstop with 3 columns.

I get following error message with the code above:

 
This error occurs:
No qualified path for file: ***
 
The error takes place here:
LOAD Company, SubField(CompanyID,'-',1) as text, Subfield(CompanyID,'-',2) as ID, Revenue FROM hello1(1).xls
 (xml, embedded labels, table is Sheet1)

 

 

 

marcus_sommer
MVP & Luminary
MVP & Luminary

The fileformat is wrong. By a xls-file it must be biff and by xlsx-file it must be ooxml and not xml. Further you need to ensure that your filepath is correct. The easiest way to get this is just to use the file-wizard.

- Marcus