56792_it-IT

liliconcom

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

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 for (j=1, j if ( dtC:start_date[i] >= dtP:action_date[j] >= dtC:start_date[i] + hr,
 dtC:PC_TA[i] = dtP:chart_value[j],
 dtC:PC_TA[i] =0

 );
 );
);

 

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

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).

 

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

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

 

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

@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. 

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

Thanks all

I have corrected the script 
the if () is looking for a date in dtP that is greater up to one hour from dtC date and to sum the assigned values.

 

hr = 3600;

dtC << new column ( "PC_TA", numaric,continuous,format( "Best",8),);

for (i=1, i <= Nrow(dtC), i++,
     for (r=1, j <= Nrow(dtP), r++,
           if ( dtC:start_date[i] <= dtP:action_date[r] <= dtC:start_date[i] + hr,
                dtC:PC_TA[i] = dtC:PC_TA[i] + dtP:chart_value[r]
                 );
     );
);

still, I get the PC_TA column all empty

 

 

uday_guntupalli

if I take the first rows from the two table and subtract I suppose to get  ~49 Hrs ~176400 Sec.

with the Hour () function I get 22

dtP:action_date[1] - dtC:start_date[1];

/*:

-177221


hour(dtP:action_date[1]) - hour(dtC:start_date[1]);

/*:

22

 

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

@AdamChoen, 
     Couple of observations and suggestions : 

1. Do not use "j" as a loop variable. JMP has an inbuilt function "J" and it is advisable not to use "j" as a variable. 

2. Your if condition seems to have a problem. 
Typically, your if condition should like what I have below, however you have 1 complete and 1 incomplete condition in your if statement joined without a logical operator. 

 

dtC = data table ("cycles");
dtP = data table ("74_edt_PC");
hr = 3600;

dtC << new column ( "PC_TA", numaric,continuous,format( "Best",8),);

For( i = 1, i < N Row( dtC ), i++,
 For( r = 1, r < N Row( dtP ), r++,
  If( dtC:start_date[i] >= dtP:action_date[r],
   dtC:PC_TA[i] = dtP:chart_value[r],
   dtC:PC_TA[i] = 0
 
  )
 )
);

For e.g. if you wanted to compare the hours of the 2 timestamps, you should instead consider: 

dtC = data table ("cycles");
dtP = data table ("74_edt_PC");
hr = 3600;

dtC << new column ( "PC_TA", numaric,continuous,format( "Best",8),);

For( i = 1, i < N Row( dtC ), i++,
 For( r = 1, r < N Row( dtP ), r++,
  If( hour(dtC:start_date[i]) >= hour(dtP:action_date[r]),
   dtC:PC_TA[i] = dtP:chart_value[r],
   dtC:PC_TA[i] = 0
 
  )
 )
);

 

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

I believe you have your If() statement logic slightly off.  Your second comparison should be less than or equal to the Start_Date plus 1 hour

If( dtC:start_date[i] >= dtP:action_date[j] <= dtC:start_date[i] + hr,
 dtC:PC_TA[i] = dtP:chart_value[j],
 0
);