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!
Best answer by francoisView original