Loading multi-period row data files using Data Management
The ability to load multi-period data with the Period and/or Year
in the rows is not particularly new (since release 16.10), however it is
something that most people seem to forget is possible in Data Management. This
really is something worth remembering as it eliminates the need to either split
the data files into one file per month or try to pivot the month into the
columns.
Important Note! When executing the data load rule, it will still only run for the selecting periods, with the period mappings being picked up from the columns.
In order to set this up, let’s firstly look at how to add the
Period and Year columns to the import format.
Add the Period and Year columns using the Add ->
Source Period Row dropdown, and input:
- The Source Column name – e.g. PERIOD
- The Field Number (file column) – e.g. 3
- Any required expressions for that field
As these are Period/Year dimensions, the mappings are not held in
the usual Data Load Mappings area. Instead they are configured in the Period
Mappings, under the Source Mapping tab.
Select the Source System as File to add a new mapping
calendar for loading the data file.
In the example below, I’ve named the new calendar as HCM_File
– this will be referenced in the Data Load Rule later.
Using the Period Key as a reference, map each system period to the
format of the Period and Year in the data file. For example, above Apr-19
maps to Period: Apr and Year: 2019 in the file.
As they are manually entered, these mappings would need to be updated manually when new years are added to the system.
As they are manually entered, these mappings would need to be updated manually when new years are added to the system.
Once the new calendar has been created, an extra option will appear on
the Data Load Rule for Period Mapping Type, with the following options:
- Default – uses the default mappings on the ‘Global Mapping’ tab
- Explicit – uses the custom ‘Source Mapping’ calendar specified in the Calendar field
To use the custom period mapping calendar, in this example we select
the Explicit period mapping type and the new calendar HCM_File, as below:
Important Note! When executing the data load rule, it will still only run for the selecting periods, with the period mappings being picked up from the columns.
For example, consider a file which contains data for Jan, Feb and
Mar periods (all year 2019):
- If the rule is executed for Jan-19 to Feb-19, then only Jan and Feb data will be loaded into the system
- If the rule is executed for Oct-19 then no data will be loaded
- If the rule is executed for Jan-19 to Mar-19, all data will be loaded
Happy loading!
Thank you for your tutorial.
ReplyDeleteIs it possible to use global mapping rather than source mapping?
Our mapping list already exists in the global mapping and it's too long.
If not, is it possible to load the source mapping list rather then inputting it line by line.
Hi, if the Period column in the data file is in the correct format, then you can use the "Default" period mapping type and the load willuse the global mappings. The key is that the period in the file is the same as the global mapping "Period Name" (e.g. Jul-20) so that it can be mapped to the correct Period & Year.
DeleteHi,
DeleteThank you for you quick reply!
The period column format in the file is JAN/FEB/MAR...
The year format is 2019/2020/2021...
We added the period column in the import format of the rule.
Our global mapping is similar to your source mapping: for each period name, we define a target period month and year target. Example:
Period Key | Prior period key | Period name | Target period Month | Year target
12/31/2019 | 11/30/2019 | Dec-19 | DEC | FY19
It didn't work with the global mapping. All lines were rejected because of the ERROR_INVALID_PERIOD
But if we use Source mapping with the same configuration it works.
Best regards
Thank you, Lidya. I can't thank you enough. Actually, for me, It worked becaue the Period/Year is in the correct format. I appreciate your help. Have a good day!
DeleteHello! Hoping you can help. In my source file, I have one column to define Period/Year. It is in the format of 2020M1 -> 2020M12. However, I get an error when I try to populate the source mapping with anything other than a valid date format.
ReplyDeleteHi, the "GL Period" column needs to be populated with the period format in the file, and should let you enter any format. You don't have to have a separate year format if this in given in the period column. The period key and name map to the Planning period/year and must be specific date formats.
DeleteFor example (if you follow calendar year so Jan=M1) the mapping would would be as below:
- Period Key = 01/01/2020
- Period Name = Jan-20
- GL Period = 2020M1
Then Jan-20 maps to Jan Period and year FY20 using the global period mappings.
Remember to add the period column in the import format and select Explicit in the data load rule.
I am really very happy for the nice approach is visible in this blog and thank you very much for using the nice technology in this blog.I really appreciate your work.If you require about GST Registration Consultants in delhi | Auditors in delhi please click on it.
ReplyDeleteHi, the column Period Number accept the value 0?, for example 01, 02, etc or only takes 1,2 ... as valid?
ReplyDeleteIf the period name in the file column is 01, 02 etc, then enter this in the "GL Period" column to map to the correct Planning period (Apr, May etc.)
DeleteSAP Grc online training
ReplyDeleteSAP Secrity online training
oracle sql plsql online training
go langaunage online training
azure online training
java online training
salesforce online training
Hi, we have Jan-Dec calendar in GL and Apr-Mar calendar in target FCCS application, Inside Data Management(Not in GL or FCCS) Do we have a chance to calculate or filter/mapping tricks in order to uploading cumulative periods from GL and load into One month YTD (e.g Jan-Feb-Mar-Apr sum of all periods up to current into the Apr YTD)?
ReplyDeleteThis comment has been removed by the author.
ReplyDelete