So I'm trying to create a fact table with a certain measure. I have two dimensions:
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:
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.
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?
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Watch the recorded webinar!
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema
Pick up some tips and tricks with Context Variables