Skip to main content
Question

Data historization

  • 4 July 2024
  • 2 replies
  • 107 views

Hi there, 

I’m facing a recurrent problem and can’t seem to have a solution to fix it. 

My problem is the following : 

I monitor my P&L statement in Pigment, this P&L is directly based on my general ledger. The general ledger is imported once a month, and includes all the months of the year, although the entries for previous months only change marginally. 

Each month, the general ledger is mapped, with a supplier column (which does not exist in the original import); a formula in Pigment is used to map according to the wording of the entry. However, sometimes the formula does not recognise the supplier correctly, or does not map it. 
As a result, I end up doing manual mapping to correct inconsistencies.  

However, when I re-import the general ledger the following month, the manual adjustments are overwritten and I end up with unmapped suppliers. How can I ensure that they aren’t overwritten ?  How can I keep track of manual adjustments?  
 

I'd like to make it clear that we don't want to integrate only the current month of the general ledger but all of it. 

2 replies

Lehtohen
Rank
Userlevel 2
Badge +1

Hello Marie, 

I recently came upon a similar challenge and found a pretty flexible solution. 

I added an image of the architecture to make the solution more clear. 

In general the steps are as follow:

  1. You have your transaction list with rows that require Supplier mapping. In my example I did not include a Supplier column in the Transactional list at all since using a formulas in a transactional list is not the best practice
  2. Export rows where you want to make your manual supplier mapping
  3. Import these rows to a new transactional lists called “Counter Bookings” and “Mappings”
    1. The “Counter Bookings” list is needed to automatically eliminate duplicate entries
    2. Create the manual Supplier mapping to the “Mappings” list 
  4. Create metrics from each transactional list
    1. Use -1 multiplier in the “Counter Bookings” metric in order to create the counter booking
    2. Use BY SUM to sum values from the original “Transactional Data” and “Counter Bookings” to a “No Supplier” item. 
    3. Sum values from the “Mappings” transactional data to the mapped supplier
  5. Create a “Final”-metric where you sum up values from all three metrics. Use this metric to push-values to other models. 

 

Lehtohen
G
Userlevel 1
Badge +1

Hi @MarieJ , 

You can get around this issue by including a Unique ID per record of the Transaction block.

Suppose you have two csv files, as shown below:

 

  1. Set up your transaction block with the Unique_ID property having Unique Item values toggled on.

 

  1. Import your first csv file with the following configuration: 
  1. Input your manual supplier mapping. 
  1. Import the second csv file with the following configuration:
    Additional options > Clear Items prior to import
    (Save this configuration for all future imports as well)
  1. Your end result should have two new records, GL03 with an updated Amount, and your Supplier Manual Overrides from earlier remaining unchanged.

 

Reply


Ask your question here!

Terms & ConditionsCookie settings