Time difference for instances where two different fields have the same value

[FourFuel]

Hi

Please could you assist me . I  am struggling to find out how to determine the average time difference, per company, per month, for instances where parameter_value_1 = tx_report_field1.  I want to use this for generating a chart table. Below is a sample of my uploaded data with three instances marked as an example of the instances I am looking at. 

[BjoernWollny]

Are these unique entries? So is there a 1:1 relation?

Without any further details I would suggest to make use of the load script:

1. Load table and set parameter_value_1 as Key

2. Load table again and set tx_report_field1 as Key + rename field Date_Time to Date_Time_Report

3. left join the data from step 2. to data from step 1.

afterwards you should have one table where you can easily calculate the difference.

[FourFuel]

Thankyou very much for the feedback.    These are unique entries with a difinitive 1:1 relationship.

I am not familiar with the load script, an will have to study it so I can apply wjhat you have suggested

[BjoernWollny]

Hi,

if you provide the data, we can work something out together. Following you find a first idea of the load script to meet your requirements. Afterwards you should be easily able to use formula like Date_Time_End-Date_Time_Start

//load data without 
Table1:
Load
	Company,
    Date_Time as Date_Time_End, //Rename the Date_Time field
    statement_text,
    text,
    parameter_value_1 as Key, //this will be the key to match the entries for start date
    tx_report_field1,
    tx_face_value
from [some libary/myFile];


left Join
Load
	tx_report_field1 as Key, //key to match entries
    Date_Time as Date_Time_Start //rename fields
from [some libary/myFile];

[FourFuel]

Hi 

Thanks again for the assistance.  I really appreciate your time and effort. Below is the script I loaded.  It loaded without errors. However there were an inordinate number of lines loaded, at a point in time, that do not make sense to me-see the load progress screen shot, that I have marked and that I have pasted in after the script.

Just to make sure, maybe I explained this poorly,  that I only want the time differences for the "numerical" numbers(6 numbers long) and not the text. If one looks at parameter_value_1, it only has blanks or the numerical numbers(6 numbers long) and no text.  So the dates/data associated with only the numerical values in parameter_value_1 must be compared to the dates/data which have the same numerical number in tx-report_field1. Its only the numerical numbers (6 numbers long,) in both  parameter_value_1 and tx_report_field_1 that are unique and have a 1:1 relationship. Its only these where I need to determine the time difference.

//load data without

Table1:

Load

requester_trading_name, //refers to Company in my example
tx_date_time as Date_Time_End, //Rename the Date_Time field //tx_date_time refers to Date_Time in my example
statement_text,
text,
parameter_value_1 as Key, //this will be the key to match the entries for start date
tx_report_field1,
tx_face_value

FROM [lib://AttachedFiles/Q4BN Management Dashboard.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

left Join

Load

tx_report_field1 as Key, //key to match entries
tx_date_time as Date_Time_Start //rename fields

FROM [lib://AttachedFiles/Q4BN Management Dashboard.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);