Data Integration
Hi all,
I need to import data from files provided by business. Since the files contain duplicates and i have to import all records so i am using numbered list for the import.
But moving forward, I need to import single unique keys and ignore the duplicates but retain their values. I am finding difficulty in accomplishing this
The above image shows the source data imported to numbered lists.
Point to note here - Long Description are texts and have length more than 100 hence concatenation is not working if used as code for non numbered lists.
The desired output is as below
Is there any work around to accomplish this.
Many Thanks!
Best Answer
-
Yeah, you're close. Here's how you can handle this. Turn each of the Country, Channel, SubSector, Benefit Segment, & Long Description into smart keys. For simplicity purposes, you can make Country, Channel, SubSector, and Benefit Segment 3 letter codes. Long description should not be used as a primary key, rather, use the SKU or some other identifier for that. Long description is a a property of the SKU. So let's use your example and, again, we'll use @rob_marshall's approach which is best practice. It also uses DISCO which is the most efficient way to leverage the Anaplan calculation engine.
- A list for each primary key: country, channel, subsector, benefit and SKU.
- A standard list for your combinations
- A system module for each of those lists above including the combination list
- A transaction module that only uses the the combination list and TIME if you need it.
- Use a second module to parse out the combination so you can relate each record to whatever list you want.
When you load this data, only one record will be created because you only have one combination. The values automatically get added together. Compare to your numbered list this is much smaller and more supportable. Remember, each list item requires 500 Bytes of overhead plus the length of your name and code. Notice our transaction module only has two columns. One for combination and one for the transaction data. Much smaller! Here's a super simple example.
Data loads with no errors and duplicates are consolidated.
System module allows us to FINDITEM and relate combinations to any of the dimensions.
Here we sum up the data by country!
2
Answers
-
Great use case. I suggest starting with a standard list (not numbered). Map the primary keys to a unique code and then either concatenate the codes or map the codes to a smart key of some kind. Smart key is the most efficient. See @rob_marshall best practice article on how to import transaction data in combination with a system module to parse the smart key. Avoid using keys that have no meaning if you can - it makes support very difficult in the long run.
Also, note that by using a structured list instead of numbered, Anaplan will automatically add numeric values where there are duplicates, solving your initial interest.
1 -
Thanks for the timely reply Jared.
But the issue still persists.
Let me give you the complete picture. Say I imported all records to a non numbered list. I now have around 1 million records.
Now for these there are 5 properties - Country, Channel, SubSector, Benefit Segment, & Long Description.
The Long Description is a text formatted property with character length over 100 and if i try to concatenate it by taking say 'x' or 'y' number of characters by using Left/Right functions so that for code length < 60 then all unique keys are not getting imported.Some keys still getting left out due to redundancy.
In the above screenshot I have 32 records with same properties viz., Country, channel Subsector, Ben Seg & Long Desc for all these 32 are same but their code(from list) are different.
Now if i try to create a new key by concatenation it is affecting other records and I am unable to use FINDITEM correctly and hance my values are not getting summed up.
Can you please share me alternatives to create smart keys.
Thanks in advance0 -
Thanks for the detailed solution.
My issue is solved!! 😀1