Compare or Search on Column/Field Names in a same IDEA database
G'day All,
Recently, I got a request to do a search or comparison between field names/columns in a same IDEA database files. Refer to example below, I need to search the word apple in column 2 from column 1. What is the @function that could be use here or do I need a script to do that?
Column1 Column2
1. orange 1. banana
2. apple 2. mango
3. pear 3. peach
4. grape 4. apple
Regards, William
Thanks Brian on the
Thanks Brian on the instructions given. However, guess the result is not I'm expecting of. Actually, my example is referring to some banking stuff. Let say the value date is 15/06/2015. I would like to know whether the maturity date has been renewed a month later, i.e. 15/07/2015 or it could be earlier or later than the maturity date. It do make a difference if it is renewed early or later as the interest rate would different. I try use the Compare function, but it does not allow user to put in any criteria.
Hope you understand where I'm coming from. Thanks again.
William
HI William,
HI William,
Yes, the @CompIf statements allows for criteria, you could have something like this @CompIf(@Age(DATE2, DATE1) < 30, 1, @Age(DATE2, DATE1) < 45, 2) So if the two dates are less than 30 days apart return 1, if they are less than 45 (but over 30) days return 2 and then you could do an extract.
Hi Brian,
Hi Brian,
Similar question to the one above. I am trying to get the amount of time elapsed between items in different rows. So I want to compare two different events (which each have their own row), when the date and employee are the same, and the amount of time elapsed from the start of one event to the end of the other event. Any thoughts?
Thanks!
HI hartmt,
HI hartmt,
You will have to use the @GetNextValue or @GetPreviousValue to perform something like this. I will use the General Ledger-GL file as an example as it comes with your installation of IDEA.
I will use the Accoung Num, Date and Time fields for this example:
I first need to put it in the order that I want. I have two options I can do an index on Account Num, Date and Time fields but the problem with the index is if you change it the the duration doesn't make sense anymore so I will instead create a new file by sorting the Account Num, Date and Time fields. This is what the sort looks like:
You can see from the above that the items are in a good order to do a comparison between the records. Next I will create a virtual numeric field to calculate the duration between the records. In order to calculate the duration the Account Num and Date must be the same or else it will return 0.
In my equation I test to see if it is the first record and if so then the duration is 0. I then compare the account num and date fields and if they are the same then I calculate the difference in the time, this returns the number of seconds between the two.
The new fields looks like this:
Hope this helps.
Brian
Hi William,
Hi William,
I am not sure if I understand exactly what you are asking. So if you are searching for apple do you want to know that apple appear as the second record in column1 and the fourth record in column2?
One way you can do a search is use the IDEA search function (it is located under the Data ribbon as part of the search). In the Search function you can select one or more databases and one or more fields to search on. So in your case you would select your database and then column1 and column2 and the search parameter would be apple.
If you are trying to match up the columns another way would be to add a recno() to your file, then split it in two, with the first file contains the record number and column1 and the second file containing record number and column2. You would then perform a join matching column1 to column2 and including the record number so you would then have a new file showing how the two columns relate and the record number of the transaction.
So I am not sure if that is what you are looking for, if not let me know.
Thanks
Brian