0

I'm working in Pentaho 4.4.1-GA (Kettle / PDI). The database is Postgres.

I need to be able to insert multiple records into a fact table based on the fields that come from a single record. The single record contains fields:

productcode1, price1
productcode2, price2
productcode3, price3
...
productcode10,price10

So if there was a value for each of the 10 productcode / prices then I'd need to insert a total of 10 records into the fact table. If there were values for 4 of the combinations, then I'd need to insert 4 records into the fact table, etcetera. All field values for the fact records would be identical except for the PK (generated by sequence), product codes, and prices.

I figure that I need some type of looping construct which would let me check whether or not a value was present for each productx field, and if so, do an insert/update step on the fact table with the desired field values. I'm just not sure how to do this in Pentaho.

Any ideas? All suggestions are welcome :)

Thank You,

Rakesh

Rakesh Thakoordyal
  • 1
  • "*The single record contains fields*" - those are multiple _rows_ with multiple columns - not just one row ("record"). Where is that "single record" (row) coming from? –  Mar 05 '15 at 07:30
  • The single record is being derived from an XML column in a table using an SQL select statement in a Table Input step. The XML has elements for each of the productCode / productPrice combinations and I create a field for each of them when querying (this makes up the single record). – Rakesh Thakoordyal Mar 05 '15 at 11:39

1 Answers1

0

Could you give a sample input and output for your scenario??

From your example data I can infer that if there are 10 different product codes and only 4 product prices you want to have 4 records inserted into your table. Is that so?

Well for a start you can add a constant value of 1 to those records by filtering for NOT NULL and then use an Group BY Step to count the number of 1's. This would give you the count. BTW it would be helpful if you could provide more details on what columns you would be loading as there are ways to make a PDI transformation execute multiple times

ash4stacked
  • 21
  • 1