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

[srikotla]

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]

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]

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]

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]

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: