I have a table with the following columns: Account Number and Cost Center.
我有一个表，有以下列:账号和成本中心。
I want to create a new column called Target Account which is based on the derivation of these two. I already have that mapping. For example:
我想创建一个名为Target Account的新列，它基于这两个的推导。我已经有了映射。例如:
Account numbers
10000*
10010*
10020*
10030*
10040*
10080*
are all mapped to Target 1110
是否都映射到目标1110 ?
Similarly,
同样的,
10050*
10060*
10070*
10095*
are all mapped to Target 1115. I need to first figure out how to create this mapping.
都映射到目标1115。我需要首先弄清楚如何创建这个映射。
After this it gets a little complicated, because Cost Center comes into picture for few records.
在此之后，它变得有点复杂，因为成本中心出现了很少的记录。
For example based on the combination of Account and Cost Center, we have the following mapping of Target as shown in the link below:
例如基于Account和Cost Center的结合，我们有如下的目标映射，如下链接所示:
Similarly, we have:
同样的,我们有:
And so on...
等等……
Can somebody please help me to create this mapping in SQL or Excel or PowerBi?
谁能帮我用SQL或Excel或PowerBi创建这个映射吗?
1 个解决方案
#1
1
If this is a one-off exercise (e.g. data migration), then honestly, I would create the complete, detailed mapping table for each source account number and cost center combination. A simple join on this mapping table would provide you with the target account number.
如果这是一次性的操作(例如数据迁移)，那么老实说，我将为每个源帐户号和成本中心组合创建完整的、详细的映射表。这个映射表上的一个简单连接将提供目标帐户号。
If this is a recurring exercise (e.g. reporting by different accounting standards) and managing the complete detailed mapping table would be too cumbersome, then I would create patterns in the mapping table using % or _ characters. In this case also a simple join on the mapping table would give you the desired target account number, but this time I would use like operator to map the source data to the target.
如果这是一个重复的练习(例如按照不同的会计标准进行报告)，并且管理完整的详细的映射表会太麻烦，那么我将使用%或_字符在映射表中创建模式。在这种情况下，映射表上的一个简单连接也会给出所需的目标帐户编号，但是这次我将使用like操作符将源数据映射到目标。
#1
1
If this is a one-off exercise (e.g. data migration), then honestly, I would create the complete, detailed mapping table for each source account number and cost center combination. A simple join on this mapping table would provide you with the target account number.
如果这是一次性的操作(例如数据迁移)，那么老实说，我将为每个源帐户号和成本中心组合创建完整的、详细的映射表。这个映射表上的一个简单连接将提供目标帐户号。
If this is a recurring exercise (e.g. reporting by different accounting standards) and managing the complete detailed mapping table would be too cumbersome, then I would create patterns in the mapping table using % or _ characters. In this case also a simple join on the mapping table would give you the desired target account number, but this time I would use like operator to map the source data to the target.
如果这是一个重复的练习(例如按照不同的会计标准进行报告)，并且管理完整的详细的映射表会太麻烦，那么我将使用%或_字符在映射表中创建模式。在这种情况下，映射表上的一个简单连接也会给出所需的目标帐户编号，但是这次我将使用like操作符将源数据映射到目标。