Skip to main content
Solved

Create a unique property list automatically from a Transaction Table

  • November 28, 2022
  • 5 replies
  • 970 views
Wallflower
Forum|alt.badge.img

Hello Pigment Community,

 

We are importing our General Ledger in a Transaction Table. In this Transaction Table, we have 2 key information that we are trying to rework:

  • Document Number
  • CC ID (= it’s the department ID)

 

Our issue is that we have multiple lines for the same document number with multiple CC ID.

 

Our objective is to get a unique list of document numbers with a unique CC ID attached to it. We know in advance that a specific list of CC IDs are not relevant and that we want to exclude them.

 

If you look at the screenshot below, we have multiple lines for the Document Number “JOUFR12797”. We want to attach to this document number which CC ID it is flowing to.

 

Our standard CC ID is a set of 3 digits, followed by the name of the department. Here, it is “340 - Customer Experience”.

 

The others CC IDs that you see on the screenshot (“4 - P&L Allocation..” and “5 - P&L allocation”) are not relevant and we don’t want them in our unique list.

 

What we want is basically a unique list of Document Number with the standard 3 digits CC ID attached, excluding the CC ID “4 - P&L Allocation”, “5 - P&L Allocation”.

Here the correct result will be:

  • Property 1: JOUFR12797
  • Property 2: 340 - Customer Experience

 

Could you please let us know how we can automatically have this unique list, looking for what’s the CC ID excluding some specific items that we know in advance?

 

Thank you for your help,

If you need any additional information or if it’s unclear let me know!

Steven

Best answer by francois

Hi!
It looks like the Document Number property is a text-format property. I would first try to turn that into a dimension, otherwise you won’t be able to do any multi-dimensional work based on it.

I’ve recreated a similar example on my side:

To get the values from the transaction, you’ll have to call something like this:

'Document Transaction'.'CC ID'[BY FIRSTNONBLANK: 'Document Transaction'.Document]

By using either the FIRSTNONBLANK or LASTNONBLANK modifier, you can bring the metadata from the transaction at the Document Number dimension (to be used in a dimension for example).

As you can see though, in the screen capture, we bring the wrong value. Let’s filter that out! To do that, we’ll have to first set a filter at the Department level.

I think you can easily flag which department is relevant or not based on their name. I would create a new boolean property in the Department dimension, either with a manual input or based on its name like this

FIND("-", Department.Name) = 5

Then, we can simply use this boolean as a filter to only gather the relevant departments. This is the result I get:

'Document Transaction'.'CC ID'
  [FILTER: 'Document Transaction'.'CC ID'.'Is Fine Department?']
  [BY FIRSTNONBLANK: 'Document Transaction'.Document]

You could add this formula as a property of the Document dimension, and then use it back in the transaction, like so:

Just chaining properties here

Let me know if you have any other question on the topic.

    5 replies

    francois
    Employee
    Forum|alt.badge.img+13
    • francoisEmployee
    • Employee
    • Answer
    • November 28, 2022

    Hi!
    It looks like the Document Number property is a text-format property. I would first try to turn that into a dimension, otherwise you won’t be able to do any multi-dimensional work based on it.

    I’ve recreated a similar example on my side:

    To get the values from the transaction, you’ll have to call something like this:

    'Document Transaction'.'CC ID'[BY FIRSTNONBLANK: 'Document Transaction'.Document]

    By using either the FIRSTNONBLANK or LASTNONBLANK modifier, you can bring the metadata from the transaction at the Document Number dimension (to be used in a dimension for example).

    As you can see though, in the screen capture, we bring the wrong value. Let’s filter that out! To do that, we’ll have to first set a filter at the Department level.

    I think you can easily flag which department is relevant or not based on their name. I would create a new boolean property in the Department dimension, either with a manual input or based on its name like this

    FIND("-", Department.Name) = 5

    Then, we can simply use this boolean as a filter to only gather the relevant departments. This is the result I get:

    'Document Transaction'.'CC ID'
      [FILTER: 'Document Transaction'.'CC ID'.'Is Fine Department?']
      [BY FIRSTNONBLANK: 'Document Transaction'.Document]

    You could add this formula as a property of the Document dimension, and then use it back in the transaction, like so:

    Just chaining properties here

    Let me know if you have any other question on the topic.

    François (Pigment)
      Wallflower
      Forum|alt.badge.img
      • Steven CongarWallflower
      • Author
      • Wallflower
      • November 28, 2022

      Hi François,

      Thank you very much for the detailed answer, it is what we were looking for.

      Much appreciated!!

       

      Steven

        Omer Nahum
        Master Trendsetter
        Forum|alt.badge.img+2
        • Omer NahumMaster Trendsetter
        • Master Trendsetter
        • November 29, 2022

        Hi Francois,

        Thank you so much for the detailed answer! It was really helpful!

        Omer.

          Wallflower
          • Bar.PoratWallflower
          • Wallflower
          • November 30, 2022

          Hi Francois,

          Thank you so much for the detailed answer! It was really helpful!

          Omer.

          Thank you, very helpful!!

          Chris
          Wallflower
          Forum|alt.badge.img+14
          • ChrisWallflower
          • Wallflower
          • January 12, 2023

          @Omer Nahum @Bar.Porat @Steven Congar     As expected @francois  always comes up with a great answer. 

          Just an FYI, we did add a new functionality called Automatically generated unique ID for unique properties set to Integer.  Not nearly as sleek as Francois's solution but another option for you to have. 

          Chris

            Ask your question here!

            Terms & ConditionsAccessibility statement