2013-03-01
I am really enjoying working here at DataConsulting. As I am the latest joiner to the team, Robin has kindly given me the opportunity to produce this month’s edition of the regular “Coffee Corner”. Although I highly doubt I can do a better job than Robin, I do hope you enjoy it as much as I did producing it.
You may be wondering what is Qaj? And how does this relate to coffee corner? I’m not taking over, I promise; Robin suggested that I add my spin on the regular coffee corner. Qaj is a traditional type of tea that is a regular hot drink in countries such as Turkey and Kosovo. It just happens to be my favourite hot drink also; if the opportunity ever arises I would recommend you give it a try! This month’s “Qaj” Corner is a brief overview of my favourite ACL function and command so far, I’m sure that you, yourself have those favourite ones that you use on regular basis.
Although a fairly new user of ACL, I can confidently say that the range of functions and commands that ACL offers is very impressive. Not only are they easy to get your head around, they are not as complicated as I initially assumed they might have been! They are also very efficient and effective, especially with large amounts of data that previously would have taken me hours or even days to do in other programmes such as Excel.
Now that I am equipped with a good understanding of the ACL functions and commands, I can confidently identify the Bes-t one(s). These of course do not indicate they are the best but an indication of my favourite ones.
Although a very simple function, CTOD( ) has made quite an impression on me. I really like the fact that I can easily change the date format in order to be able to compare the dates across different files so much easier. With a simple formula the whole process becomes more automated and a lot simpler!
Syntax:
CTOD( field<,date_format_if not_yymmdd>)
For example, I have two different files that I would compare on the date field. As the files have originated in different systems, their date formats are different. In the first file, the dates are stored in “DD-MMM-YYYY” format, e.g. 14-May-2012. In the second file, the dates are in “DD/MM/YYYY” format, e.g. 14/05/2012. As these two fields have been imported as character fields I can use the CTOD function as follows:
File 1: CTOD(date, “dd-mmm-yyyy”)
And
File 2: CTOD(date, “dd/mm/yyyy”)
My favourite command would definitely have to be the Join command. By far this has impressed me the most. It is so easy to use and very efficient! Comparing this to Excel, I would have the whole trouble of V-lookup which was a process in itself! Now with the ACL Join command I can easily convert and transfer all the files into one readable format which enables analysis at a much faster pace!
Additionally, the Join command offers a range of methods to interpret two tables, these I have listed below. This is really effective when analysing data based on differing objectives that may require different records to be analysed.
This example Join is based on the following tables:
Matched Primary Records
Creates one output record for every record in the primary table that has a match in the secondary table.
Unmatched Primary Records
Creates one output record for every record in the Primary table that does not have a match in the secondary table.
Matched Primary Records Include All Primary Records
Creates an output of the records that are in the primary table and an additional record for the ones that are not matched in the secondary table.
Note that the unmatched numeric data from the secondary table appears as a zero amount. This is the best option for showing overpayments, as we can see that there are some employees that have been paid, that don’t appear in the HR table.
Matched Primary Records Include All Secondary Records
Creates an output of the records that are in the secondary table and an additional record for the ones that are not matched in the primary table.
Matched Primary Records Include All Primary And Secondary Records
Creates an output for every record in the primary table and that has a match in the secondary table, additionally unmatched records from the secondary table. If we were testing for underpayments as well as overpayments, this option has all the information.
Many-to-many
Creates one output record for every match between records in the primary and secondary tables.
Planning the join is essential in order to get it correct and save the hassle of redoing if join doesn’t produce the data as required. As a fairly new user these preferences might alter slightly, but I am quite confident Join will remain a favourite command for a while with me.
If you would like to know furthermore and how to produce the Join, or any other questions, please feel free to contact me and I will be more than happy to help.
