Exploring TM1 - a Chartertech Company
Search
Close this search box.

"Joining" Text Files in a TM1 Turbo Integrator Process

I was faced with an interesting situation yesterday. I needed to load information from a header and a detail table into TM1. So, just do a join in the TI, I said to myself. And then in the middle of the night I realised that just isn’t possible to do a join in a TI when the source data is text or csv files. This blog post deals with how we overcame the problem of how to join text files in a Turbo Integrator process.

If we were using a relational database as the source to load data from multiple tables in a single query into TM1 we would have a query on our TI that includes a join in something like this:

select header.invoice_number, header.customer_id, header.invoice_date, detail.sku, detail.invoice_line, detail.quantity, detail.sales, detail.cost 

from header, detail 

where header.invoice_number = detail.invoice_number

Note this has a join on the invoice_number field. With a text or csv file as the source, we just can’t do joins like this.

Solution to “Creating a Join” to Load from Multiple Text Files

So how did we load load data into TM1 from multiple text files as the source as if we were creating a join? It was a two step process:

  1. In the TI reading the header file we created a temporary dimension using invoice number as the element. Then we assigned attributes for customer ID and invoice date.
  2. Then in the TI that reads the detail table we simply read each record, getting the invoice number, invoice line, quantity, sales and cost and then use ATTRS to look up our temporary dimension to get the customer ID and invoice date for the current record’s invoice number. Then, we deleted the temporary dimension created in the first step.

Snappo! We were able to join text files via a Turbo Integrator process.

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

John Vaughan

John Vaughan

John is a CPA, MBA and has been a Performance Management consultant for over 25 years. He is the founder of ExploringTM1 and highly regarded for his experience combining financial management with corporate planning, reporting and analysis. He lives in Sydney with his wife, two of his three children, their cat, Freckles, a bunch of chooks and some fish. John is a sports nut, who played rugby until he was 40, started playing football at 54 and loves being outdoors.

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In