Loading Data with many-to-one relationship

rhightower
rhightower
in Modeling

I believe this is straightforward, however I haven't been able to word my searches to find what I'm looking for - so either it doesn't exist or I cannot find it. Going to throw this out there:

 

Our general ledger Actuals have individual (unique) accounts which have a many-to-one relationship with account groupings (I.e. individual cash gl accounts all map to one parent "cash"). My model only needs parent level grouping values to build forecast scenarios. I've built two flat lists: L1 for the individual accounts and the other, L2 for the parents/grouping with the parent relationship identified. Is there not a way to use the L2 Groupings list in the Actuals Module where I can load the individual GL account Actuals that auto map to the L2 groupings? Is there a better approach to solving sparsity issues by avoiding GL account details? We're always adding accounts, so I don't want something that is a burden to update.

 

Additional goal: I also have a separate "product" list I am applying in the Module, but I only need it to apply to maybe 5% of the GL accounts (so a large waste of space), but if I can make this mapping GL accounts to parent groupings I'll save space since about 50% of the groupings (L2) have the product code.

Tagged:

Answers

  • JaredDolich
    JaredDolich

    @rhightower 

    Great question. If I understand you correctly you want to import L1 (lowest level) transaction values but only use L2 (Parent) in the analysis.

    • My first recommendation, if you haven't already, is that you read @rob_marshall best practice article on how to load transaction data, quite possibly the best article written. Rob Will show you the most efficient way to load the transaction data - don't fear sparsity - sometimes that's a good thing believe it or not. Your transactions, by the way, will have zero sparsity if you follow Rob's advice.
    • Load the data above then use a module to SUM the transactions up to L2. And, you're all set!

    For question #2

    • Sounds like a good use-case for list subset. Would that work?

     

Categories