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]

the data is in the rows under the column, so number 1 worked because it was the excel column name. number 2 is specific to the data in the rows under.

[OmarBenSalem]

this affects all the rows (values) of the column, not the column Name ; the column name is specified with the AS NewColumnName..

[Anonymous]

ok so for instance, in the data in the rows,

I have:

Shoes - OU

and I just want:

Shoes

Can you input in the code as to how it would remove that from all the values that are Shoe - OU?

Thanks for your time

And would this go before the column I bring in within the load script? the column is [Sports]

[OmarBenSalem]

in the script, you'll have sthing like this:

load

col1,

col2,

Sports,

otherCol

from source;

you change it as follow

load

col1,

col2,

trim(subfield(Sports,'-',1)) as Sports,

otherCol

from source;

what subfield do is to select only one part of the entire value based on a seerator; in this case; the epeator is '-' and we want to keep only the first part (Shoes)

But since the original is : Shoes - OU (with space)

this will return Shoes (with a space after the last 's') ; to delete all the spaces: we use trim .

if you have some values as follow:

Sport Shoes - OU

and thus you want to keep the space between Sport and Shoes and just delete the last one (after the s of shoes)

do as follow:

replace(subfield(Sports,'-',1),right(subfield(Sports,'-',1)),'') as Sports

https://help.qlik.com/fr-FR/sense/3.0/Subsystems/Hub/Content/Scripting/StringFunctions/Replace.htm

hope this helps !

[Anonymous]

You rock man!!!

There are some that are just " OU" and not sure how to remove them...

Most are gone cause the - ones are gone now.

Got any ideas on how to remove the ones that are not done by a -?

if no since some have middle spaces that is ok this will work

[amarnath_krishn]

Could you please post some sample of the ones that you need removed?

The extract of data post applying subfield would help, if in excel format possible.

Or a few items to grasp the problem.

[Anonymous]

Hello Amarnath,

I marked as correct as the problem has been solved.

Thanks for your help though!