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!
Part 2 of a series on Context Variables
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema