Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load Script Edits from Excel file - Qlik June 2017

Hello Qlikies!

1.

I have a question that I am trying to resolve. I am working in Qlik Sense June 2017, and am trying to solve an issue with the way my   data is gathered for me.

Currently, my data comes into an excel file and has a column with both a unique ID and product name in it. I have requested these be separated, but this is something that cannot happen.

Is there a way that I can separate the items from one excel column to come into qlik sense as two separate items? I assume this can be done in the load script? Help here would be AMAZING

2.

Also, can we eliminate part of the data from a different column? We have a column that is product capability that comes in with a unique identifier after the name that we wish we could just delete. I think it is the last 5 spaces in the name in the excel column.

We would normally just do these things manually; however, the data will be coming in using this format going forward.

Thanks so much for all you guys and gals do.

My best!

1 Solution

Accepted Solutions
Ricardo_Gerhard
Employee
Employee

Question 1: SubField - script and chart function ‒ Qlik Sense

Ricardo Gerhard
OEM Solution Architect
LATAM

View solution in original post

16 Replies
Anonymous
Not applicable
Author

In number 1, the unique ID and product name are separated by a "-"


thanks,

TKendrick20
Partner - Specialist
Partner - Specialist

You should be able to do this by going to the Data Manager in the upper left corner.

Data Manager.png

Then look at the table view:

Data Manager - Tables.PNG

Edit the table with the product id/name field:

Data Manager - Edit.PNG

Add a calculated field (one for each of product id and product name)

Data Manager - Calc Field.PNG

Then, you'll have to add the expression to extract the substrings. This can be very easy if your unique ID always has the same length. Then, for instance, the Unique ID field could be LEFT([FieldName],5) and the Product ID could be RIGHT(FieldName, Len(FieldName)-6).

Hope this helps!

Ricardo_Gerhard
Employee
Employee

Question 1: SubField - script and chart function ‒ Qlik Sense

Ricardo Gerhard
OEM Solution Architect
LATAM
Ricardo_Gerhard
Employee
Employee

Question 2: TextBetween - script and chart function ‒ Qlik Sense

Ricardo Gerhard
OEM Solution Architect
LATAM
TKendrick20
Partner - Specialist
Partner - Specialist

Unfortunately you can't use the SubField function in the formula for the calculated field for some reason.

Data Manager - String Functions.PNG

It has a list of functions that it allows and typing in your own formula causes an error. A pretty weird constraint if you ask me.

OmarBenSalem

1) Yes you can:

suppose your Field name is Field : and as an example one value is as follow:

Field : 1-FirstValue

to seperate it; do this:

load

a,

b,

Subfield(Field,'-',1) as Id,

Subfield(Field,'-',2) as Name,

Field

from source;

drop field Field;


the Id would be 1

the Name FirstValue


2) LOAD replace(YourColumn,Right(YourColumn,5),'') as NewName


this will delete the last 5 caracters

Anonymous
Not applicable
Author

So for number 2, I have some text in the excel column that is like:

Shows - OU

I want it to just pull in

Shows

I have tried the above but cannot seem to get it right, and does it matter where it is in the load editor? Either before or after the overall column where the data is?

Thanks

OmarBenSalem

same as 1 ;

trim(subfield(YourField,'-',1)) as newField

Anonymous
Not applicable
Author

Even though this data is in one of the columns and not the column header?