Can We create a Variable in Mapper

Hello Everyone,

I am trying to create an incremental addition let say i have 2 columns empl_id and salary so in target i want to derive third column with cumulative salary.

I can do it by using variable, have done it using Different tool but never implemented it in snaplogic

Hence the question can we create Variable in mapper?

any help is appreciated

Can you give more information about where the cumulative salary column will come from? If it’s just a sum of all the salary columns and grouped by the employee id, then you could use the Aggregate snap.

Here’s an example I put together based on what I thought you were looking to do.

Feel free to download this pipeline and try it out.

Aggregate-Example_2019_07_15.slp (3.9 KB)

Hi tlikarish,

Appreciate your reply but that does not solves my query.

Below is the data:-
employee_id,salary
10, 1000
20, 2000
30, 3000
40, 5000

Expected O/p
employee_id,salary,Cumulative salary
10, 1000,1000
20, 2000,3000
30, 3000,6000
40, 5000,11000

Hope now its clear , Also i tried to Google for variable in mapper but there was no comments or article on it.
Can we not create a variable in mapper? because these can be done in different ETL tools using variable.

The aggregate snap has a concat function that should concatenate the values instead of sum them. I think that maybe closer to what you’re trying to achieve.

You could also take a look at the Group By Field snap, but that may be too different from how you’d like to transform the data.

In regards to variable creation, there is nothing like that in the mapper. You can transform document properties using expressions and write them to new properties, but there aren’t any variables you can use.

No tlikarish, i dont think concat function will help me.

Anyways there should be a new way ,Only the problem is we have to figure it out :smiley:

Thank you for your help, Appreciate it.

If you’re not finding any of the specialized snaps are fitting your use case, you could try the Script snap to implement the transformation in JavaScrip/Python/Ruby. Hope you can find something that solves your problem.

1 Like

@Karan_Mhatre
@tlikarish
I have wrote down a script to fulfill your request, please refer to attached pipeline and screenshot.


test_2019_07_19.slp (11.0 KB)

1 Like

@bhupender.singh

Understood your code with the help of Google bcoz i dont know python but was able to relate it
Thank you for your reply .
but can i do the same thing with csv file?

Again thank you mann for your help

@Karan_Mhatre yes you can use file reader and csv formatter to get output similar to the Json generator I have used.

1 Like

Hi @Karan_Mhatre, @bhupender.singh

Please find the solution for the above usecase without using the script snap.

The attached pipeline solves your purpose.
variableInMapper_2019_07_24.slp (9.1 KB)

1 Like

Hello Anil,

Really appreciate your effort to find a solution, But i think there is wrong calculation done for cumulative salary.

Can you please check your logic again , please refer to my expected o/p comment?

Thank you again for showing us a way!

Hi @Karan_Mhatre,

Yeah the logic was not right,

Please find the latest attached pipeline which solves your requirement.

variableInMapper_2019_07_29.slp (9.6 KB)

1 Like

Hello @Anil , I am trying to implement similar logic but i need to group by two fields instead of one unlike in the above example. Instead of only emp_id , I have two fields i need to consider, suppose product_name and product_date , and then find the cumulative sum for this combo . Can you please help me with the logic for this?

@Preeta Sure…

Can you send the input file and what is the output expected…

Thank you responding . Here is the input and expected output
count Date product
2 2008-01-31 ABC
1 2008-07-31 ABC
2 2009-01-31 ABC
1 2009-04-30 ABC
3 2009-07-31 ABC
3 2009-10-31 GEF
5 2010-01-31 GEF
1 2010-04-30 GEF
3 2010-07-31 GEF

output :
Sum Date product
2 2008-01-31 ABC
3 2008-07-31 ABC
5 2009-01-31 ABC
6 2009-04-30 ABC
9 2009-07-31 ABC
3 2009-10-31 GEF
8 2010-01-31 GEF
9 2010-04-30 GEF
12 2010-07-31 GEF

Hi Preeta,

One way to solve this is to concatenate the two fields into a single one in a mapper snap, then use that to aggregate the data like the solution for a single field.

Best,
Tim

Hi Tim,
Thank you for your reply.
Even if I concatenate , the issue here would be that it would generate a cumulative sum for all the records while I want to get the cumulative sum grouped by each product. In the above solution , the ruuid is same for all the records while i need a new ruuid for each group of same product records.

Hope that makes sense.

Thanks ,
Preeta

Sorry should have looked at your example before replying. I see what you’re saying… I think I’d probably group all the elements into an array by the product, then use that array to build the cumulative results, and then map everything back to the original structure. Not sure if that makes sense, but let me give it a try.

Think this should work for your use case. Think some of the expressions could be simplified, but hopefully clear enough to follow.

accum-by-date_2019_09_17.slp (9.5 KB)

@PreetavariableInMapperNew_2019_09_18.slp (9.9 KB)

Please find the attached pipeline.