Incrementing a measure using data from a dimension

Anonymous — Thu, 23 May 2019 20:30:07 GMT

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.

Re: Incrementing a measure using data from a dimension

nfz11 — Tue, 28 May 2019 08:14:30 GMT

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?

topic Re: Incrementing a measure using data from a dimension in Talend Studio

Incrementing a measure using data from a dimension

Re: Incrementing a measure using data from a dimension