CODEWITHSUNDEEP
datecomparisonconditional-statementsfieldspotfire
PineNuts0
PineNuts0

Reputation: 5234

TIBCO Spotifre Analyst v7.10: Conditionally Create Two New Calculated Columns Based on Comparison of Two Date Columns

I currently have the sample table below in Spotfire Analyst 7.10. I want to create two new calculated columns using the data below (Insert --> Calculated Column). 

Id      Date        cDate       Value         
--------------------------------------------
A       10/17/2017  10/18/2017     10               
A       10/17/2017  10/14/2017     15    
A       10/17/2017  10/8/2017      -2       
B       11/19/2017  11/19/2017     4      
B       11/19/2017  11/30/2017     3

Below is the logic I'm trying to implement:

a. for the SAME Id value, find the cDate that is equal to OR, if not equal, then closest to the date in Date column

b. the cDate selected CANNOT be greater than the Date value

c. create new column (Rel Date) that has the date fulfilling criteria "a" and "b" listed for each row associated with same Id

d. create another new column (Value) that pulls in Value associated with date selected in criteria "c"

Below is the output table I want after above logic is implemented: 

Id      Date        cDate          Rel Date       Value                      
----------------------------------------------------------
A       10/17/2017  10/18/2017     10/14/2017     15               
A       10/17/2017  10/14/2017     10/14/2017     15 
A       10/17/2017  10/8/2017      10/14/2017     15  
B       11/19/2017  11/19/2017     10/19/2017     4 
B       11/19/2017  11/30/2017     10/19/2017     4

Upvotes: 1

Views: 121

Answers (1)

ksp585
ksp585

Reputation: 1770

@PineNuts0- Here's how you could achieve this.

Step 1: Add a calculated column [diff] with the expression below which finds the difference between [Date] and [cDate] over column [Id].

If(Days([cDate] - Max([Date]) over (Intersect([Id],[Date])))>0,null,Days([cDate] - Max([Date]) over (Intersect([Id],[Date]))))

Step 2: Add another calculated column [MAX_diff] with the expression below which finds the max of [diff] column over [Id] and [Date].

Max([diff]) over (Intersect([Id],[Date]))

Step 3: Now, add a calculated column [GET_VAL] to get the value based on max difference between [Date] and [cDate].

Max([Value]) over (Intersect([MAX_diff],[Id]))

Step 4: Finally, create a calculated column [Rel Date] to get [cDate] based on the values we got in the previous step.

DateAdd("dd",max([diff]) over (Intersect([Id],[Date])),[Date])

Note: The calculated columns which were created in Step 1 and 2 can run in the background and it is not required to display them in the table.

Here is the final output:

enter image description here

Upvotes: 1

Related Questions