topic Re: How to find space in the columns in QlikView

How to find space in the columns

Wed, 25 Nov 2020 16:16:04 GMT

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:

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

Re: How to find space in the columns

Anil_Babu_Samineni — Tue, 06 Dec 2016 05:10:29 GMT

Ename is your data

What was the Expected Output from that

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

Re: How to find space in the columns

tresB — Tue, 06 Dec 2016 05:13:22 GMT

Try with subfield(), like:

Load

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

Subfield(EBNAME, ' ', 2) as SecondName

Re: How to find space in the columns

Tue, 06 Dec 2016 05:16:08 GMT

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

Re: How to find space in the columns

ishanbhatt — Tue, 06 Dec 2016 05:20:48 GMT

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.

Re: How to find space in the columns

Tue, 06 Dec 2016 05:21:13 GMT

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

Re: How to find space in the columns

Tue, 06 Dec 2016 05:22:23 GMT

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

But this is the expected result.

Re: How to find space in the columns

ishanbhatt — Tue, 06 Dec 2016 05:25:53 GMT

Hi,

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

Hope this helpful.

Re: How to find space in the columns

Tue, 06 Dec 2016 05:28:38 GMT

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

Re: How to find space in the columns

Anil_Babu_Samineni — Tue, 06 Dec 2016 05:37:34 GMT

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;

Re: How to find space in the columns

ishanbhatt — Tue, 06 Dec 2016 05:41:22 GMT

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

Re: How to find space in the columns

Tue, 06 Dec 2016 05:41:38 GMT

Hi Anil, I am get wrong result

Re: How to find space in the columns

Lech_Miszkiewicz — Tue, 06 Dec 2016 05:49:51 GMT

!`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

Re: How to find space in the columns

Anil_Babu_Samineni — Tue, 06 Dec 2016 05:59:00 GMT

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

Re: How to find space in the columns

Tue, 06 Dec 2016 06:02:14 GMT

Hi anil,

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

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

Re: How to find space in the columns

Anil_Babu_Samineni — Tue, 06 Dec 2016 06:12:27 GMT

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

Re: How to find space in the columns

Tue, 06 Dec 2016 06:41:07 GMT

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

Re: How to find space in the columns

Anil_Babu_Samineni — Tue, 06 Dec 2016 07:26:30 GMT

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

Re: How to find space in the columns

Tue, 06 Dec 2016 08:03:23 GMT

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.

Re: How to find space in the columns

Lech_Miszkiewicz — Wed, 07 Dec 2016 22:23:10 GMT

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