Function or Script

Ahmed

Forums

Hi All,
I have a field that contains a list of number separated by (,), what i need is : create a lot of field depended of how number contained in it ,
for ex : 3554249, 3554250, 3554251, 3554252  and the result that i'd like is for ex :

A
B
C
D

3554249
3554250
3554251
3554252

Thank you.

Brian Element Fri, 09/22/2017 - 07:31

Hi Ahmed,

I guess that depends if you know the maximum number of fields you need to create.  If you know that there are only four items that needs to be split out that you can do this in a function.  If this case you would create four virtual character fields and use the following equations:

@SimpleSplit(COLUMN_1, "", 1, ",")

@SimpleSplit(COLUMN_1, ",", 1, ",")

@SimpleSplit(COLUMN_1, ",", 2, ",")

@SimpleSplit(COLUMN_1, "", 1, ",", 1)

And these equations will give you the following:

If you don't know the maximum number of items then you will have to create a script as the script would have to dynamixally add on an additional field each time it finds a new item.

Ahmed Mon, 09/25/2017 - 03:23

Hi Brian,
the maximum numbers of field that i need is 2014, however; each line is different than the other as you can see it in the snapshot, also ; in my idea the "," is replaced by ';' so for me the correcte formule is :
@SimpleSplit(COLUMN_1; ""; 1; ","; 1)
Thank you.

Brian Element Mon, 09/25/2017 - 08:21

Hi Ahmed,

I think you are going to have a few problems.  The first is that there is a limiation of 1024 characters for a character field, so if you are bringing in the information with 2014 fields it will cut-off at the first 1024 characters.  The next is there is a limitation of 1000 fields in idea, so if you need 2014 fields it won't be possible or it will have to be broken up over 3 databases.

So one alternative is if you don't mind it being broken up over 3 databases is to create a script that reads your information directly and then parses it out to the different databases.

The difference in the list separator is based on the regional language of your computer, I am in English so my list separator is a comma, other languages use a semi-colon.

Thanks

Brian

 

Steven Luciani Mon, 09/25/2017 - 09:25

Let's go back to your source data. What type of file are you importing into IDEA to get this field? Are there other fields, or just one field in your data file. If the numbering plan is your only field then a delimited import with a comma as your delimiter will create all the fields you need during import.

Ahmed Mon, 09/25/2017 - 09:35

Hi Brian; 
By following your advice, i make some changes in txt  in order to split the database into several field and after i uploded it to idea (see the snapshot).
so now, is't possible to do (Vlookup) with this field using visuel connector ? 
For the difference in the list separator ; my computer is in french language.
Thanks.
 

Brian Element Mon, 09/25/2017 - 09:52

Hi Ahmed,

Yes you should be able to use visual connector, can you be more specific what you plan to do?

Thanks

Brian

Ahmed Mon, 09/25/2017 - 10:32

Hi Brian,
I have two database, in the first; it contains the numbering plans from 1 to 2015 and their rates as you can see it in the snapshot, and in the other database i have only the numbering plan, so i need to connect the B database with the A database to give me the rate.
for ex : i'm going to tell him to lokup for this number in the A database in the column from 1 to 2015 and if you find it give the rate.
Thanks.

Brian Element Mon, 09/25/2017 - 11:07

Hi Ahmed,

For the lookup it would be better to have only two fields.  One with the Rate and one with the Number Plan such as this.

You can then do a join using the join function or the visual connector based on the numbering plan.

Ahmed Mon, 09/25/2017 - 11:27

Hi Brian, 
Yes, you're right but now , i'm going to deal with another problem is:  how to get it like your example ? summarization by rate ? 
Thanks.

Brian Element Mon, 09/25/2017 - 13:37

Hi Ahmed,

I have attached a script that should help you out.  What it does is first create a new file for each Numbering_Plan field along with the Rate field and then it renames all the Number_Plan fields to Numbering_Plan and finally it appends all the files together.  It works on the file that is currently open so there are no dialogs.

This should take your file, create files for each number field and then append them back together into one large file in which you will have two fields, rate and numbering plan.

let me know how it goes.

Brian