Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Trim Parse Substring data in load script?

Hello all,

I am in a unique situation that I need guidance with. I currently have two load scripts for two separate excel work books.

There are hundreds of rows of data in each workbook.

The first data sheet has a list of names that reads like below:

Rodgers, Aaron A ~ Last name - comma - space - First name - space - Middle Initial


The load script is simply:

[ExampleSheet1]:

LOAD

  [Name],

     etc...,

     etc...;

FROM [...]

(ooxml, embedded labels, table is ...);

My second data sheet needs to match the first, (make the two relational), but they are quite different.

The second data sheet has a list of names that reads like below:

"this is a space"Aaron Rodgers ~ SPACE - First name - space - Last name

There is a space before the name..

Here is the second load script

[ExampleSheet2]:

LOAD

     [Name],

     etc...,

     etc...;

FROM [...]

(ooxml, embedded labels, table is ...);

#1. I will need to trim off the middle initial from the first data sheet. To do this I assume I need to search for the second space, then trim anything off after that. Make it change from:

Rodgers, Aaron A ----> Rodgers, Aaron


#2. Change the names from firstName, lastName to be lastName, firstName.  While removing the space before the name.

Make it change from:

' 'Aaron Rodgers ----> Rodgers, Aaron


So, not sure if this can be done in the load script?  The simple Ltrim, Rtrim, Trim, SubString etc... aren't working for me in the load scripts.  Ltrim([Name]) gives me an error when I try to use it.  No clue how to parse the second sheet accordingly. Thank you in advance!

1 Solution

Accepted Solutions

Re: Trim Parse Substring data in load script?

May be like this:

#1

SubField(Trim(Name), ' ', 1) & ' ' &  SubField(Trim(Name), ' ', 2)

#2

SubField(Trim(Name), ' ', 2) & ', ' & SubField(Trim(Name), ' ', 1)

2 Replies

Re: Trim Parse Substring data in load script?

May be like this:

#1

SubField(Trim(Name), ' ', 1) & ' ' &  SubField(Trim(Name), ' ', 2)

#2

SubField(Trim(Name), ' ', 2) & ', ' & SubField(Trim(Name), ' ', 1)

Not applicable

Re: Trim Parse Substring data in load script?

Perfect!  Thank you!