For simplicity we have one table keyed on product, a transaction table with a foreign key link to the product table and a transaction date. There is also a product_conversion table keyed on product and effective date which contains a conversion rate.
We need to be able to do a lookup of the product from the transaction table to the product and product_conversion tables to get the appropriate conversion rate for the transaction date. Rather than doing some complex SQL lookup, I thought that if we could create a staged table keyed on product with two multivalued arrays of effective date and conversion rates then I could do the lookup on product, returning these two arrays, to be passed to a transform along with the transaction date. The transform would locate the transaction date in the effective date array (assuming a sort order), using the position to return the corresponding conversion rate array element.
There are:
200,000 transaction records per month processed nightly. I.e. Each night the current months transactions are reprocessed.
60000 product records.
200,000 conversion records.
We could sort the product_conversion table by product by effective date and then build up the two arrays which is concatenated in memory and written out to the stages table. This staged table could then be loaded into memory.
Phil Walker
DataStage Consultant
Project Clarity
Carter Holt Harvey Forests
DDI: 96094
MOD: 025 835 197
EMAIL: phil.walker@chh.co.nz
-----Original Message-----
From: David Barham
Sent: Thursday, 23 November 2000 19:06
To: Phil.Walker@chh.co.nz
Subject: RE: Vertical pivoting
Phil,
Where is the data coming from and where is it going to?
While I understand what you are saying (I think) about varying number of
columns per row, is there a maximum or upper limit to "n"? Keeping in mind
that DataStage (to the best of my knowledge) cannot handle a varying record
layout for the output, I am assuming that unused columns are blank or null.
On the other hand, if your output is CSV and you truly want
(theoretically)
unlimited columns then the output may need to be a single column built up as
a string within the transform.
Anyway, if there is a maximum value for "n" then this may influence the
design.
Cheers,
David Barham
Information Technology Consultant
CoalMIS Project
Anglo Coal Australia Pty Ltd
Brisbane, Australia
e-mail: David.Barham@anglocoal.com.au
phone: 07 3834 1275
mobile: 0412 169 518
fax: 07 3834 1389
This e-mail and any files transmitted with it may be confidential and are
intended solely for the use of the individual or entity to whom they are
addressed. If you have received this e-mail in error, please notify the
sender by return e-mail, and delete this e-mail from your in-box. Do not
copy it to anybody else.
-----Original Message-----
From: Walker, Phil (Forests Manukau) [SMTP:Phil.Walker@chh.co.nz]
Sent: Thursday, November 23, 2000 2:17 PM
To: informix-datastage@oliver.com
Subject: Vertical pivoting
A challenge for someone? (Ray
)We need to construct a job whereby we vertically pivot n * m input rows into
m rows with n columns.
The n for each m may not be the same! That is m1 may have 10 columns whereas
m2 may have 1 and m3 may have 4.
I know I could do this by writing some preprocess code, but can we, or has
someone done it via designer gui and some smart logic in a transform or
something similar? If so, how performant was it?
Regards,
Phil Walker
DataStage Consultant
Project Clarity
Carter Holt Harvey Forests
DDI: 96094
MOD: 025 835 197
EMAIL: phil.walker@chh.co.nz