Incrementing a measure using data from a dimension

Highlighted
Three Stars

Incrementing a measure using data from a dimension

So I'm trying to create a fact table with a certain measure. I have two dimensions:

 

Stagiaire(id_stagiaire;nom;prenom;departement;id_projet) 

and

Pointage(id_pointage;id_stagiaire;date)

 

With the second being my relationship table (it's a one to many relationship).

 

In the fact table I want to add a measure called number_of_delays which is calculated using the date column in Pointage. The idea is this:

 

I compare the date of each row with a given format, in my case this:

TalendDate.compareDate(row3.date,TalendDate.parseDate("HH:mm", "09:00"),"HH:mm")

 

Then according to the result (it return either -1,0 or 1 and I'm testing with 1) I increment a global variable using a local variable (Var.nbr). This is what I made so far:

 

The value of the measure is calculated using

TalendDate.compareDate(
	row3.date,
	TalendDate.parseDate("HH:mm", "09:00"),
	"HH:mm") == 1 ? (
		Numeric.sequence("Var.nbr",1,1)
	):(int)globalMap.get("Stagiaire"+row3.id_stagiaire) 

This partially works. Instead of incrementing the number of delays for each Stagiaire id, it just keeps incrementing the same variable and putting in the database.

 

How can I achieve this?

 

TL;DR I need to increment the value of a measure according to an id using a test, so if the test of user 1 was passed 2 out of 3 times, the value would be 2, and if the test of user 2 passed 5 times out of 9, the value will be 5 and so on.

Nine Stars

Re: Incrementing a measure using data from a dimension

Is Var.nbr a variable in a tMap?  It is possible it is resetting because the lifespan of that variable is only for one row.  Try using a variable in the globalMap.  Can you show a screenshot of your tMap map editor?

--
Please give Kudos and mark topics as solved where appropriate.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog