Hello,
I wasn't sure where to look for this task. I have a database and it has 9 columns however, they are repeat column headers with their own sets of data. For example,
Check Num 1 Date 1 Amount 1 Check Num 2 Date 2 Amount 2 Check Num 3 Date 3 Amount 3
How would I combine the Check Num column into 1 column as well as the other 2 column types into their own.
Any guidance would be appreciated. Thanks!
mwiger
Offline
Last seen: 2 months 1 week ago
Joined: 07/29/2021 - 10:01
You will need to create a virtual field to do this. Here are the steps.
Double click within your data window to bring up the field manipulation.
Select Add button from the buttons on the right hand side.
Give the new field field name, under type if it is a character field you have three options, Virtual Character, Editable Character and Character. Vitrual Character allows you to go make changes to the formula later on if you made a mistake, Editable will store the result which you can then change and Character just stores the value so you can't change it. Which field you choose will depend on what you want to do.
Enter the length, make sure it is long enough to hold all the fields you want to combine.
Click on the parameter to bring up the equation editor and your equation would be something like:
CHECK_NUM1 + CHECK_NUM2 + CHECK_NUM3
Exit the equation editor and then select OK in the field manipulation to create the new field.
Hi Brian,
Thanks for the reply but I don't think I explained it very well. What I have is in Check_Num1 a list of 100 check numbers and then in Check_Num2 I have another list of 100 check numbers. What I am looking to do is create a new field called Check_Num and put the list from Check_Num1 and Check_Num2 into that column. So in the new Check_Num column would be the 200 total check numbers.
What you show is just concatenating the check numbers. I need them to be all in one column individually.
How large are each check number as an IDEA character field can only hold 1024 characters I believe. If your check number are too large then this might not work.
Maybe it would be helpful if I understood why you want all the check numbers in the same field. This is what I am understanding you want to do:
Check_Num1 Check_Num2
100001 200001
100002 200002
100003 200003
So your combined would be something like this?
Combined_Check_Num
100001-200001-100002-200002-100003-200003
Is this what you are looking for?
The data came in how your example shows. What I would like to do is have it look like
Combined_Check_Num
100001
100002
100003
200001
200002
200003
The way that I have done it currently is to do three extractions, one for each Check_Num column and then append them together. I wasn't sure if there was a faster way of accomplishing this. There are also two other sets of columns that I need to do as well, a date and amount. So it would look like
Check Num 1 Date 1 Amount 1 Check Num 2 Date 2 Amount 2 Check Num 3 Date 3 Amount 3
100001 10/04 14.04 200001 10/15 24.35 300001 10/24 35.43
100002 11/23 100.00 200002 09/23 12.34 30002 10/05 245.97
and I would like it to look like
Check_Num Date Amount
100001 10/04 14.04
100002 11/23 100.00
200001 10/15 24.35
200002 09/23 12.34
300001 10/24 35.43
300002 10/05 245.97
I hope that helps.
Thanks again for your help!
You are doing it how I would do it. The only way to speed it up is to create a script to do it for you. If this is something you do all the time then I would use the recorder to record it and then run the script each time you need it done.
Thanks Brian! I appreciate all your help.