Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to find space in the columns

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 NameSecond name
SteveJobs
AdamHill

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

20 Replies
Anil_Babu_Samineni

First of All, Here you are talking about Ename. Can you try this for me

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),'Ename') as FieldName,

subfield($(i),' ',1) as [First Name],

subfield($(i),' ',-1) as [Second Name]

AutoGenerate 1

;

NEXT i

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful