How to find space in the columns

[#1]

Hi guys,

I have a script where i ma loading files from the excel and concatenating it. I have list of field names and counts too.

Now i need to find the spaces in the records of the column and make them separate list

Expected: If record found it should display two more lists with First name and surname if the column has space.

Example:

IF

Ename
Steve jobs
Adam Smith
Yogesh Kumar
Qlik Community

FOUND Then make it

First Name	Second name
Steve	Jobs
Adam	Hill

Below is my Script for your assistance

Concat: 

LOAD 0 as TempField 

AutoGenerate 0; 

FOR Each vfile in FileList('C:\Users\w_318460\Desktop\New folder\*.xlsx') 

  Concatenate (Concat)

  LOAD * From [$(vfile)] (ooxml, embedded labels, table is Sheet1); 

NEXT vfile 

DROP Field TempField;

FOR i = 1 to NoOfFields('Concat')

Fields:

LOAD

  FieldName($(i),'Concat') as FieldName

AutoGenerate 1

;

NEXT i

Regards,

Yogesh

[Anil_Babu_Samineni]

Ename is your data

What was the Expected Output from that

I didn't understand how you get the Adam & Hill

[tresesco]

Try with subfield(), like:

Load

          Subfield(EBNAME, ' ', 1) as FirstName,

          Subfield(EBNAME, ' ', 2) as SecondName

[#4]

I dont know what is der inside the table...that was just example.. incase i find that kind of column with space i need to create new records like we use in sub fields

[ishanbhatt]

Hi Yogesh,

Use subfield function to identify the space and make two different fields from the one field.

below is the example.

EName_Temp:

LOAD * INLINE [

    Ename

    Steve jobs

    Adam Smith

    Yogesh Kumar

    Qlik Community

];

EName:

Load

  Subfield(Ename,' ',1) as FirstName,

  Subfield(Ename,' ',-1) as LastNAme

Resident

  EName_Temp;

Below is the screenshot for the same.

Ishan.

[#6]

Hi tresesco,

I can use that if i already know the column name..But hea i donno column names...I am doing load*..please go through my script

[#7]

Ishan...i need to get that result using my old script...not separate one..

But this is the expected result.

[ishanbhatt]

Hi,

You can also use subfield parameter value 2 instead of -1. I have attached Example qvw here.

Hope this helpful.

[#9]

Yea thank you. I know the concept of subfield..We need to use loops here not subfield function as a whole

[Anil_Babu_Samineni]

Ok, Try this instead of that

For each Variable in FileList('C:\Users\w_318460\Desktop\New folder\*.xlsx')

    TEMP_FILE:

    LOAD

        subfield('$(Variable)','\',-1) as [First Name],

        FileTime('$(Variable)') as [Second Name] autogenerate 1;

Next Variable;