Separately Group Rows then Re-group them in Exact Order

KFajardo · Post by **KFajardo** » Thu May 21, 2015 12:56 am

ShaneMuir · Post by **ShaneMuir** » Thu May 21, 2015 4:15 am

Welcome.

If the data is as simple as below - you could use a stage variable to geneate a surrogate key value each time a new Type1 is received and then apply that to each other type until such time as the next Type1 is received. That surrogate key could be a number, or the "project" name itself.

So read the flat file in sequentially (to ensure that all the project information ends up on the same node) and then in a transformer stage set a stage variable to identify when the incoming record is a type1.
When that field is a type 1 set your surrogate key, then apply that surrogate key as a new output column to each input row. Each time a new type1 arrives update your surrogate key value.

Hope this helps.

KFajardo · Post by **KFajardo** » Thu May 21, 2015 1:30 pm

Thank you, Shanemuir.

I think you answered the question. But I don't have an idea on how to implement it. Can you shed some light on giving an example or a formula so that I can visualize on how the stage variable will work. Because I only know simple stage variables for now and I don't know to implement a stage variable that will toke effect until another TYPE1 will occur and then get a substring of the next Project name.

Again, thank you. You are really helping me on this.

-keith

KFajardo · Post by **KFajardo** » Thu May 21, 2015 3:57 pm

Will i use a Loop here? I have searched the net on my problem and looping seems to be a possible solution. Can you help me on this?

Hoping for your generous help.

-keith

ray.wurlod · Post by **ray.wurlod** » Thu May 21, 2015 4:51 pm

I don't think there's any need for a loop. Create the stage variable as, say, type Integer with an initial value of 0. Then, each time the value "Type 1" occurs, increment it.

Code: Select all

svGroupNumber  <--  If InLink.Col1 = "Type 1" Then svGroupNumber + 1 Else svGroupNumber

KFajardo · Post by **KFajardo** » Thu May 21, 2015 5:04 pm

ray.wurlod · Post by **ray.wurlod** » Thu May 21, 2015 5:07 pm

Code: Select all

svProject <--  If InLink.Col1 = "Type 1" Then InLink.Project Else svProject

KFajardo · Post by **KFajardo** » Sat May 23, 2015 2:07 pm

This is only a single column singe it is from a flat file. I want to populate the row with the "project" that is located on every type1-line only. So, i want to check on what project does every Other Types (type2, type3, etc...) belongs to.

ray.wurlod · Post by **ray.wurlod** » Sat May 23, 2015 4:28 pm

Yes? Use the value of the svProject stage variable, which contains the project from the most recently-encountered Type 1 record.

KFajardo · Post by **KFajardo** » Sun May 24, 2015 12:45 am

Ah! Now I get it! svProect's value is set into a new value everytime the it encounters a "Type1"! Thank you so much ray.wurlod!
I can now distinguish every project separately.

With all my heart, thanks again!

Keith