当枢轴行数可变时，如何使用Informatica枢轴数据? [英] How to pivot data using Informatica when you have variable amount of pivot rows?

问题描述

基于我的先前的问题，我该如何透视数据当我的数据中有可变数量的地址时，请使用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->表达式->目标表

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)

OR

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分组，并始终选择最大值， 例如

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.

这篇关于当枢轴行数可变时，如何使用Informatica枢轴数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！