Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rglenn0780
Contributor

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!

Tags (1)
1 Solution

Accepted Solutions
dcj
Valued Contributor II

Re: Load Script Edits from Excel file - Qlik June 2017

16 Replies
rglenn0780
Contributor

Re: Load Script Edits from Excel file - Qlik June 2017

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


thanks,

tkendrick20
Valued Contributor

Re: Load Script Edits from Excel file - Qlik June 2017

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!

dcj
Valued Contributor II

Re: Load Script Edits from Excel file - Qlik June 2017

dcj
Valued Contributor II

Re: Load Script Edits from Excel file - Qlik June 2017

tkendrick20
Valued Contributor

Re: Load Script Edits from Excel file - Qlik June 2017

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
Esteemed Contributor

Re: Load Script Edits from Excel file - Qlik June 2017

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

rglenn0780
Contributor

Re: Load Script Edits from Excel file - Qlik June 2017

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
Esteemed Contributor

Re: Load Script Edits from Excel file - Qlik June 2017

same as 1 ;

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

rglenn0780
Contributor

Re: Load Script Edits from Excel file - Qlik June 2017

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