How to increment the counter (variable) based on the incoming data in snaplogic

Question

How to increment the counter (variable) based on the incoming data in snaplogic.

Source Data:
CostCenter, TestCompany
Test_Cost_Center_001,Test_company_1
Test_Cost_Center_001,Test_company_2
Test_Cost_Center_002,Test_company_1
Test_Cost_Center_002,Test_company_2
Test_Cost_Center_002,Test_company_3
Test_Cost_Center_002,Test_company_4

Target Data should be:
CostCenter, TestCompany, ID
Test_Cost_Center_001,Test_company_1, 1
Test_Cost_Center_001,Test_company_2, 2
Test_Cost_Center_002,Test_company_1, 1
Test_Cost_Center_002,Test_company_2, 2
Test_Cost_Center_002,Test_company_3, 3
Test_Cost_Center_002,Test_company_4, 4
Experts over here, please help in achieving the above solution using the right snap.

tstack · Answer

srikotla:

Target Data should be:
CostCenter, TestCompany, ID
Test_Cost_Center_001,Test_company_1, 1
Test_Cost_Center_001,Test_company_2, 2
Test_Cost_Center_002,Test_company_1, 1
Test_Cost_Center_002,Test_company_2, 2

It looks like you’re trying to extract the number at the end of the “Test_company” string?  Is that right?

srikotla · Answer

Nope, I need to generate the sub sequence under each cost center, Like For cost center 001, I have 2 companies, so the sequence is provided accordingly.
I was looking for usage for variable field where I can increment the value if the prev and current record are of same cost center and reset the variable to 1 if there is change in cost center. (By providing sorted data to the variable defined).
Seems there is no concept of variables/caching data in snaplogic, please help me otherwise.

srikotla · Answer

For your ease of understanding, I changed the data:

Source Data:
CostCenter, TestCompany
Test_Cost_Center_001,Test_company_A
Test_Cost_Center_001,Test_company_B
Test_Cost_Center_002,Test_company_A
Test_Cost_Center_002,Test_company_B
Test_Cost_Center_002,Test_company_C
Test_Cost_Center_002,Test_company_D

Target Data should be:
CostCenter, TestCompany, ID
Test_Cost_Center_001,Test_company_A, 1
Test_Cost_Center_001,Test_company_B, 2
Test_Cost_Center_002,Test_company_A, 1
Test_Cost_Center_002,Test_company_B, 2
Test_Cost_Center_002,Test_company_C, 3
Test_Cost_Center_002,Test_company_D, 4

sarathmattam · Answer

srikotla:

erstanding, I changed the data:

I tried to achieve your requirement using the built-in snaps, but couldn’t find a long lived variable that can help to achieve your requirement. However, this little script can do it for you (using script snap).
       execute : function () {
       this.log.info("Executing Transform Script");
       var i = 0;
       var costCenter = 'DUMMY';
        while (this.input.hasNext()) {
            try{
                // Read the next document, wrap it in a map and write out the wrapper
                var doc = this.input.next();
                var wrapper = new java.util.HashMap();
                wrapper.put("original", doc);
                
                if (costCenter != doc.CostCenter) {
                    costCenter = doc.CostCenter;
                    i = 1;
                    wrapper.original.put("sequence",i);
                }
                else {
                    i = i + 1;
                    wrapper.original.put("sequence",i);
                }
                
                this.output.write(doc, wrapper);
                this.log.info("Transform Script finished");
               
            }
            catch(err) {
                var wrapper = new java.util.HashMap();
                wrapper.put("errorMsg", err);
                this.log.error(err);
                this.error.write(wrapper);
            }
        }
    }

Input:

Output:

srikotla · Answer

Thank you So much sarathmattam,
I’m able to achieve this using mapper in the following way.

Read the data in sorted order

Generate the sequence

Copy the data into two outputs

For one o/p, Use Aggregate to find the min sequence grouping on the cost_center

From other o/p join the data using the cost_center

Use Mapper, and in one of the port define the logic as : (Value - min_Seq)+1
Here Value is the sequence generated from the sequence snap
Min_Seq is the minimum sequence value of the respective group obtained from aggregate snap.

Forum Discussion

How to increment the counter (variable) based on the incoming data in snaplogic

Source Data:

Target Data should be:

Recent Discussions

Javascript to promote top level lists

Google Sheets Subscribe questions

Basic string transformations not working

Can we generate XML file in pretty print format using native snapLogic snaps?

Multipart Reader failure - 'content-type' was not found