Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

Labels (1)
1 Reply
nfz11
Creator III
Creator III

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?