Previous Row Repeat Given Certain Condition in Column X

lahman

Forums

Hi all!
First post :)
 
I could not find any script for this so I'll post here.
I want to create a script that picks out the following information below. Please note that all information is within one column.  Each break is one row.
I have a column that contains the following information that repeats for all the transactions, see example below.  
 
#Verification A3000 "20170301"   "Personal Expenses"   <- Header information
   #Transaction 5131 3000                                           <- Transaction information
   #Transaction 2630 -2600
   #Transcation 7301 -400
 
#Verification A3001 "20170301" "Personal Expenses"
   #Transaction 6980 3000                     <- Transaction information
   #Transaction 2640 -2600
   #Transcation 1031 -400
 
I want to convert the data to the following:
 
5131 3000 A3000 20170301 "Personal Expenses"
2630 -2600 A3000 20170301 "Personal Expenses"
7301 -400 A3000 20170301 "Personal Expenses"
5131 3000 A3001 20170301 "Personal Expenses"
2630 -2600  A3001 20170301 "Personal Expenses"
7301 -400 A3001 20170301 "Personal Expenses"
 
I've tried creating a floating layer but i've not been successful since the rows change place.
Could anyone please help me create a script for this or at least get med started? I'm not used to IDEA, only excel!
 
Regards
 

Brian Element Fri, 02/16/2018 - 07:39

Hi lahman and welcome to the site.

I took your info and created a text file from it.  Hopefully this looks something like your full file.

I am then using the Print Report and Adobe PDF option in the import assitant to import the file.

Now the information is in the Report Reader.

First thing I have to do is select the base layer, that is the layer that has the transactional data.  How I select this is just clicking and dragging my mouse over one of the lines.

In your example it looks like this will be a standard layer, so I select the first option.

I then have to select the trap, in this case since #Transaction seems to appear at the beginning of each transactions I can use that.  I then highligh the 5131 3000  plus a few extra character to make sure I get everything.  In the field details for field information I change this to Transaction and the type to Character.

I can now click on the save layer (green check mark) as this is the only info from this layer.

Next thing I want to do is get the hearder information.  Here again I will click and drag over the Verification line and select standard layer as everything seems to line up.

For the trap I can use Verification as that seems to be the start of these lines.

I then highlight the A3000 which I named VERF.   Now since this information will be added to the transactional data make sure under Attributes - Blank Cells you select Use Value From Previous Record.

You do the same for the date making sure to enter the date mask.

And finally the last item, since this didn't line up I just took all the info and it can be cleaned up in IDEA once it is imported if necessary.

Once that is complete save the layer and you can then click on the preview icon which looks like an eye, it is the last of the icons.

From the preview it looks like what you are trying to import.  Close the preview and then click on the import to IDEA (third Icon), save the template and then complete the import.  You should get something that looks liek this:

Hopefully this helped you out.