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,
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
(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
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.