Load Script Edits from Excel file - Qlik June 2017

[Anonymous]

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!

[Anonymous]

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

thanks,

[TKendrick20]

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

Then look at the table view:

Edit the table with the product id/name field:

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

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 · Accepted solution]

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

[Ricardo_Gerhard]

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

[TKendrick20]

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

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]

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]

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