search in between dates in two tables add assassin value from one to the outer

Question

Hi! I need help :) to assign a value from one table to an outer one. my trigger is the date, if the date in dtP is equal or greater in one hour from dtC then need to assign the value from dtC:chart_value to the new column (PC_TA) in dtC any help will be appreciated tables columns my code - not assign any values only Zeros and don't treat last line :( dtC = open ("...."); dtP = open ("...."); hr = 3600; dtC << new column ( "PC_TA", numaric,continuous,format( "Best",8),); for (i=1, i= dtP:action_date[j] >= dtC:start_date[i] + hr, dtC:PC_TA[i] = dtP:chart_value[j], dtC:PC_TA[i] =0 ); ); );

AdamChoen · Answer

thanks, Uday.

So if I want to use hour() I probably need to add somewhere day() and month() also because my data is 30 days back and I'm only interested to assign values that within 1 hour back, sorry for the blur description but from IP reasons I can't share too much about the data tables.

but I can give a good theoretical example of what I want to achieve:

say that the ID column in dtC is sales mans (can repeat in multiple rows) and the start date column is the date they visit in the mall.

as for dtP, the chart_value column is the sales average in a random time (action_date) at the mall.

I want to see which salesman improve the mall seals the most by sum and assign the average sales in the mall for a salesman in one hour from its visit to the mall. hope that helps.

I believe the if() is good it gives me the 1 or 0 I'm looking for when I test it, for some reason (maybe date format) there is only 0 when I run it on the tables (altho I see dates that are meeting the condition).

AdamChoen · Answer

I believe the first one also need to be flipped :)  and the if() is good.

uday_guntupalli · Answer

@AdamChoen, 
   Not really. The hour() function is getting the hour of the timestamp - so will return a value between (1-24).

The highligted line below is wrong . I dont understand from your description what you want to do but if you are checking for 2 logical conditions , there will need to be a logical operartor that joins these conditions.