Script - subfield

[margretand]

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;

[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]

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]

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

];

[sunny_talwar]

Are you forgetting to add the file name?

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]

---

@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?

[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

[margretand]

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]

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