Combining monarch tables
Hello, I am using monarch to read several daily reports, and compile all these reports into a balancing excel sheet daily. Each report produces its own table, all of these tables vary in rows and columns. Some of the tables are consistent with rows and columns daily, but there are a couple that have a different amount of rows every day. However when it does vary it is very small, one line longer or shorter.
Right now, I am copy pasting each table separately into an excel sheet that reads each table and puts them into their proper positions. When there is a variance in the amount of lines, I usually end up leaving a space blank for where the item that is missing belong. (see table).
What Monarch produces
| a | 21 |
| b | 12 |
| d | 6 |
| e | 5 |
What I input to the spreadsheet
| a | 21 |
| b | 12 |
| c | |
| d | 6 |
| e | 5 |
This process has brought two questions.
1. Can monarch keep the section c even if it cant find where c would usually be? Sometimes the reports do not have a section c but I would like to be able to copy and paste a consistent size table.
2. If I am able to make the size of the table consistent, what would be the optimal way of making all these tables 1 item that I can copy paste into an excel sheet that reads it?
Answers
-
Hi Carter,
1. The short answer is Yes. Monarch can retain the records with a full join in Data Prep Studio
2. Yes, if you create a consistent table then you can do a look up join.
I will share screenshots later when I get a chance.
1 -
What is the data type for the daily reports? If row 'c' is missing in the source data, Monarch cannot add it directly. One possible workaround would be if you have a master list of what values should be in the first column, you could use a join to pull in all of the row values and then use the matching values in the source data. If it cannot make a match, then use a default value. You would have to do this with a formula column.
1 -
Chris Porthouse said:
What is the data type for the daily reports? If row 'c' is missing in the source data, Monarch cannot add it directly. One possible workaround would be if you have a master list of what values should be in the first column, you could use a join to pull in all of the row values and then use the matching values in the source data. If it cannot make a match, then use a default value. You would have to do this with a formula column.
I will look into this, the reports are text files.
0
Altair Employee