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

[ishanbhatt]

It's good heard that you ae aware with the subfield function. You can use Subfield function in loop too.

[#3]

Hi Anil, I am get wrong result

[Lech_Miszkiewicz]

!`Hi

This can be tricky, but you still should be able to do it using SubField() function which would create 2 rows every time it hits record with first and last name.

This is just nasty workaround which quickly came to my mind but it shoudl work. (see attached XLS and QVW so you can test it)

If you are using Qlik Sense then use script from Script file

cheers

Lech

[Anil_Babu_Samineni]

What are you expecting to see, If above one is wrong?

[#6]

Hi anil,

I don't want table names...i need rows..

If u see the screen shot of Ishan's reply. You will understand

[Anil_Babu_Samineni]

That i underastand, Tresesco also gave same one. Somehow, I would ask few questions

1) Does this script related to same file i mean Ename

2) If so, Why are you using the script to split 2 names

3) If Not, Why not SubField() - Can i knnow the reason

[#8]

in this file i have column by the name Ename. But those are sample files and fields. Consider i have 100 files and i donno any column name.

This is the question which i got .ie., splitting those column if space found.

Sub field can be used. but we have to use in loops. In his example he already know the column name so he is using subfield ..now i donno column name.

In my script everything is getting loaded and i have check for every column not single column.

Thanks for your support

[Anil_Babu_Samineni]

Can you send full script with the Sample Files of Excel which situated in Same Path. and Data Model as well

[#10]

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

Now i know field names from the script. now i need to find the space in each field.If found it should split the column into two and display.

[Lech_Miszkiewicz]

Hi,

I have one question - why are you trying to achieve this in Qlik? We just reached the point that we clearly stated we dont even know what data we will pull with this script to Qlik. So my next qustion is how would you like to analyze this data without even understanding what are we bringing here at the end ( there will be bunch of random columns).

I just think we are pulling a leg with this one.

the end result of this exercise will be basicly bunch of fields with values without spaces. What analytical meaning does this have ?

Examples given by others with subfield etc. are showing a reason that you KNOW that field ENAME could have NAME and  LAST NAME and you want to create 2 or maybe 3 fields as it could also have MIDDLE NAME. However if you want to run script which will split your data set and create as many fields as possible maybe store your end result to TXT file, load it to excel and then manipulate it there (using for example split TEXT to COLUMNS functionality) then at least you would be able to control output.

It just does not feel right to do this exercise in Qlik script.

regards

Lech