Design Scenario

[taylor.hermann]

My gut feel has been that this is going to require looping, however I'm unsure where to begin. And have yet to use looping, but have done plenty of reading about it.
The input data comes in something like this:

Code: Select all

Product   Warehouse
1             20   
1             30
1             40
2             30
3             60
3             20

The output data needs to be like this:

Code: Select all

Product1
Warehouse|20
Warehouse|30
Warehouse|40
"EOR"
Product2
Warehouse|30
"EOR"
Product3
Warehouse|60
Warehouse|20
"EOR"

They want all the warehouses listed under each product. And then the text "EOR" listed at the end of each set of products.

I'm just requesting maybe someone to point me in a general area of where to begin, since I'm still semi-new to Datastage.

Thanks

[rkashyap]

This can be done thru a single threaded job.
Pass Input to a transformer. Use stage variables to detect key(Product) change. Transformer will have three output links:
- Link 1: Constraint: key-change; Output: Append 'PRODUCT' to Product-num.
- Link 2: Constraint: None; Output: Append 'WAREHOUSE|' to warehouse-num.
- Link 3: Constraint: key-change and rec-no> 1; Output: "EOR".

Combine output from transformer using Funnel stage in sequential mode. (append another "EOR" to filer-footer).

[taylor.hermann]

Thank you rkashyap. That was incredibly helpful.

However.... Upon trying to implement something similar I ran into an issue with the funnel. First off it requires that all the column names are the same, which I guess I can just name them all the same without any side effects. But the sequential mode funnel would not meet my needs. As it would do all of input 1, then input 2, then input 3.

Essentially looking like this:

Code: Select all

Product1
Product2
Product3
Warehouse|20 
Warehouse|30 
Warehouse|40
Warehouse|30 
Warehouse|60 
Warehouse|20
"EOR" 
"EOR" 
"EOR" 

Is there another option? Because none of the funnel options seem like they would work.

[rkashyap]

If Continuous funnel is also not giving desired result, then another option could be to pass columns 'Product-num and link-num (1, 2, 3 etc)' from transformer to the output. Sort 'collected' records from transformer using composite key Product-num and link-num.

[vamsi_4a6]

@rkashyap


what do u mean by link-num (1, 2, 3 etc).I do not see any column like link-num?

[ShaneMuir]

I think what @rkashyap is getting at is that you might need to add an extra columns to you outputs that can be used to determine sort order.

This could be as simple as the key column AND some sort of counter. Then you can set your funnel to sort the data. After the funnel you would drop the unrequired columns.

[taylor.hermann]

Awesome, thanks for the input everybody!!! It helped a ton.
I solved it by sorting it in the funnel as suggested. And added 2 extra columns to sort it correctly. I added the Product# to all the links and sorted it, then a dummy column to keep the links in the correct order, "A", "B", "C".
Thanks again

[vamsi_4a6]

rkashyap, what do u mean by rec-no?

[rkashyap]

Rec-No: Input record number. For a single threaded job, @INROWNUM should be ok.

[vamsi_4a6]

Awesome, thanks for the input everybody!!! It helped a ton.
I solved it by sorting it in the funnel as suggested. And added 2 extra columns to sort it correctly. I added the Product# to all the links and sorted it, then a dummy column to keep the links in the correct order, "A", "B", "C".
Thanks again

what you populated for dummy column?

[chulett]

See your quoted text, the value is right there.