[resolved] Counters based on data in tMap

[Anonymous]

Warning: I am fairly new to Talend and have mostly used tMap to import data. I'm running TOS 3.0.3 on Windows in java with FileDelimited and MSSQL input and MSSQL output. I need some help in handling an aspect of the import:
I have a .csv file with records that have different user IDs. I have a MSSQL data table of requests where I look up the user ID to get other data about the request. I'm using a tMap to transform the data and store it in multiple tables, which is working well. I also have to update the request in the MSSQL Lookup table with the number of records read for that user ID. That's where my problem is.
I can see where I could use a variable to count (numeric.sequence?) but that would be an overall count and not specific to a user id. When I try to update the data table as I go along (store updated count per user ID in that request record) hoping to get the new value to increment for each record (expression is "RCRMapping.NumReceived == null ? 1 : RCRMapping.NumReceived + 1" without the quotes of course), it doesn't seem to be doing the writes between csv records to update RCRMapping.NumReceived and I end up always with a value of 1.
Is there a way to force the writes for each read? Is there a better way to keep counts per user id?
I'm attaching a screen shot of a simplified version of my job design and my tMap (there's more to it I deleted just to play with this feature). Please let me know if this isn't clear.
Thanks for the help.
-Deb

[Anonymous]

Hello,
There something wrong in your design : you use the RCMMapingTable as Lookup and output in the same subjob
1) The data from lookup are first loaded, the data are loaded once. So tMap use a copy of data in memory or on disk.
2) When you update the output RCMMapingTable, the tMap didn't see the new data.
RCRMapping.NumReceived have always the same value !
You can create in tMap a variable ( between input and output ) and set a globalMap variable by ID to store your data.
Variable=
(Integer)globalMap.get( ID ) ==null?
RCRMapping.NumReceived == null ? globalMap.put(ID, 1) : globalMap.put(ID, RCRMapping.NumReceived +1 ) : globalMap.put(ID, (Integer)globalMap.get( ID ) +1 )

[Anonymous · Accepted solution]

Thanks so much for your help! This is really close. I didn't know about using globalMap.
I tried several contortions of the variable expression, and I keep getting "null, 1, 2" instead of "1, 2, 3" as the values of the variable for the 3 input records. Tried these two expressions most recently, both with the same results:
(Integer)globalMap.get( RCRMapping.RCRName) ==null? RCRMapping.NumReceived == null ? globalMap.put(RCRMapping.RCRName, 1) : globalMap.put(RCRMapping.RCRName, RCRMapping.NumReceived +1 ) : globalMap.put(RCRMapping. RCRName, (Integer)globalMap.get(RCRMapping.RCRName) +1 )
(Integer)globalMap.get( RCRMapping.RCRName) ==null? globalMap.put(RCRMapping. RCRName, 1) : globalMap.put(RCRMapping. RCRName, (Integer)globalMap.get(RCRMapping. RCRName) +1 )
Any suggestions?
Also, can you use more than one globalMap (using different fields and mapping to different variables)?
Thanks again for the help!
-Deb

[Anonymous]

I realize after reading some about globalMap that my question about multiple globalMaps was dumb - I think that if you can have unique values in the "key" (first parameter) then you can store values for multiple variables. Do you know where there is a good writeup explaining globalMap?
I also saw that there were spaces in my expression, but it still isn't working right. I am still getting the first value as NULL and I need it to be 0 or 1 (it does increment correctly after that). Attached is a shot of my tMap with the expression in the variable as follows:
(Integer)globalMap.get( RCRMapping.RCRName) == null ? globalMap.put(RCRMapping.RCRName, 1) : globalMap.put(RCRMapping.RCRName, (Integer)globalMap.get(RCRMapping.RCRName) +1 )
Any help would be appreciated.
-Deb

[Anonymous]

Hello guy
You can use the the method: Numeric.sequence("s1",1,1), s1 is the sequence name, the first 1 is the start index, the second 1 is the step. it will count all the rows.

Best regards
shong

[Anonymous]

Hi Shong,
Thanks for the reply. I did try that, but my issue is I need a separate counter for each user ID, and the .csv records will be interspersed with multiple records for each user ID. Is there a way to use numeric.sequence specific to a piece of data in the lookup table?
Thanks again.
-Deb

[Anonymous]

Hello Deb
Sorry, I don't understand your request. Can you try to give us an example to explain it?
Best regards
shong

[Anonymous]

I'll try. The screen shots of a representative job and tMap are in the first post.
This is the crux of the issue: I have a table that lists participants to request data from, and I'm getting data in from a .csv with multiple records (customers) per participant that contains the requested data. Example data might be:
RequestTable (Lookup Table before load)
ParticipantName ParticipantID DateReceived NumReceived
JoeSmith 111
SueDoe 222
Contents of tFileInputDelimited might be like:
ParticipantName CustomerID Answer 1 Answer 2
JoeSmith 12 yes no
JoeSmith 13 no no
JoeSmith 14 no no
SueDoe 21 yes yes
SueDoe 22 no yes
The Lookup Table is to convert participantName to ParticipantID for other reasons, but in the end, I want the RequestTable updated to reflect what was read, specifically the NumReceived (count of records for that ParticipantName) like:
ParticipantName ParticipantID DateReceived NumReceived
JoeSmith 111 8/11/09 3
SueDoe 222 8/11/09 2
And I need a data table updated that has the ordinal number of that Customer ID within the participant list (I assumed I could get the ordinal number from the "current" NumReceived for that Participant as I went along):
ParticipantID ParticipantName CustomerID CustomerNumber
JoeSmith 111 12 1
JoeSmith 111 13 2
JoeSmith 111 14 3
SueDoe 222 21 1
SueDoe 222 22 2
I know there are other ways to do this, like load it raw into a database and count it there, but I was trying to do the counts as I process the input file in the tMap where I already update several other data tables, which is the part that's working fine (I tried adding the counts recently to a working job).
Emenuet suggested a globalMap, which definitely helped and got me closer, but I don't think my syntax was right. I could use help in setting the variable expression to get the counts in a globalMap, and the expressions for NumReceived and CustomerNumber using the variable.
Let me know if this still doesn't make sense. Thanks for the help.
-Deb

[Anonymous]

Hello
First, your expression of var is not correct, you only put a value to a globalMap key, but don't get it. The if-then-else statement looks like:
condition?value1:value2
if the condition is ture, then var=value1 else var=value2
Here I create a new routine(Go to repository->code and right click on routines to create a new routine) to fix your need,

package routines;
public class forum7741 {
	static java.util.Map<String, Integer> map = new java.util.HashMap<String, Integer>();
	public static int getCount(String RCRName) {
		int count = 0;
		if (map.get(RCRName) == null) {
			map.put(RCRName, 1);
			count = 1;
		} else {
			count = map.get(RCRName) + 1;
			map.put(RCRName, count);
		}
		return count;
	}
}

So, you need delete you var, then call this routine on expression filed of column, see my screenshot.
Best regards

shong

[Anonymous]

Got it working!! Thanks so much to both emenuet and Shong. I had to do a combination of the two answers.
I added the routine as Shong wrote (worked perfectly), but since I needed to use the value in multiple tables, I had to make the call to the routine in a variable, and use the variable in multiple places. Otherwise it incremented the counter every time I called it.
So I changed "forum7741" to "PatientCount" in Shong's example and my variable expression is:
routines.PatientCount.getCount(RCRMapping.RCRName)
declared as Type int, Var name thisPatient
and my expression in the tables (reference to the count) is:
Var.thisPatient
Hope this helps others who want to do the same thing.
I am very impressed with the responsiveness and quality of the answers in this forum. Great work!
Regards,
-Deb