Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
FourFuel
Contributor
Contributor

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

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. 

 

FourFuel_0-1593006143031.png

 

4 Replies
BjoernWollny
Contributor III
Contributor III

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
Contributor
Contributor
Author

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
Contributor III
Contributor III

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
Contributor
Contributor
Author

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

FourFuel_1-1593169564232.png