根据我的早先的问题,我该如何透视数据当我的数据中有不同数量的地址时使用 Informatica PowerCenter Designer.我想从我的数据中旋转例如四个地址.这是源数据文件的结构:
Based on my earlier questions, how can I pivot data using Informatica PowerCenter Designer when I have variable amount of Addresses in my data. I would like to Pivot e.g four addresses from my data. This is the structure of the source data file:
+---------+--------------+-----------------+
| ADDR_ID | NAME | ADDRESS |
+---------+--------------+-----------------+
| 1 | John Smith | JohnsAddress1 |
| 1 | John Smith | JohnsAddress2 |
| 1 | John Smith | JohnsAddress3 |
| 2 | Adrian Smith | AdriansAddress1 |
| 2 | Adrian Smith | AdriansAddress2 |
| 3 | Ivar Smith | IvarAddress1 |
+---------+--------------+-----------------+
这应该是结果表:
+---------+--------------+-----------------+-----------------+---------------+----------+
| ADDR_ID | NAME | ADDRESS1 | ADDRESS2 | ADDRESS3 | ADDRESS4 |
+---------+--------------+-----------------+-----------------+---------------+----------+
| 1 | John Smith | JohnsAddress1 | JohnsAddress2 | JohnsAddress3 | NULL |
| 2 | Adrian Smith | AdriansAddress1 | AdriansAddress2 | NULL | NULL |
| 3 | Ivar Smith | IvarAddress1 | NULL | NULL | NULL |
+---------+--------------+-----------------+-----------------+---------------+----------+
我想我可以使用
SOURCE --> SOURCE_QUALIFIER --> SORTER --> AGGREGATOR --> EXPRESSION --> 目标表
SOURCE --> SOURCE_QUALIFIER --> SORTER --> AGGREGATOR --> EXPRESSION --> TARGET TABLE
但是我应该在 AGGREGATOR 和 EXPRESSION 转换中使用哪种端口?
But what kind of port should I use in AGGREGATOR and EXPRESSION transforms?
推荐答案
你应该使用类似这样的东西:
You should use something along the lines of this:
Source->Expression->Aggregator->Target
在表达式中,添加一个变量端口:
In the expression, add a variable port:
v_count expr: IIF(ISNULL(v_COUNT) OR v_COUNT=3, 1, v_COUNT + 1)
或
v_count expr: IIF(ADDR_ID=v_PREVIOUS_ADDR_ID, v_COUNT + 1, 1)
和3个输出端口:
o_addr1 expr: DECODE(TRUE, v_COUNT=1, ADDR_IN, NULL)
o_addr2 expr: DECODE(TRUE, v_COUNT=2, ADDR_IN, NULL)
o_addr3 expr: DECODE(TRUE, v_COUNT=3, ADDR_IN, NULL)
然后使用聚合器,按 ID 分组并始终选择 Max,例如
Then use the aggregator, group by ID and select always the Max, e.g.
agg_addr1: expr: MAX(O_ADDR1)
agg_addr2: expr: MAX(O_ADDR2)
agg_addr3: expr: MAX(O_ADDR3)
如果您需要更多非规范化端口,请添加额外端口并设置初始状态相应的 v_count 变量.
If you need more denormalized ports, add additional ports and set the initial state of the v_count variable accordingly.
相关推荐
最新文章