Jump to content
We've recently updated our Privacy Statement, available here. ×

Spotfire Add column based also on Period


Edwin Rutten 2

Recommended Posts

Hello,

I want to link the Groupname to a Custnumber and his revenue, Catch due to historical changes the Groupnames can change over time

how can i do this, based on the link by my cust names reflects the incorrect revenue numbers per Group. What must i do

 

 

 

 

Group_ID

Group_Name

START

END

Cust_ID

Cust_Name

 

 

8000091

AAAA

200901

201912

1193875

Kkkkkk

 

 

8000102

BBBB

202001

205012

1193875

Kkkkkk

 

 

8000091

AAAA

201701

205012

1191272

Pppppp

 

 

8000897

CCCC

201104

201612

1191272

Pppppp

 

 

8000102

BBBB

201501

201808

1176547

Qqqqqq

 

 

8000286

DDDD

201809

205012

1176547

Qqqqqq

 

 

8000102

BBBB

201310

205012

1191108

Rrrrrr

 

 

 

 

 

 

 

Cust_ID

Cust_Name

Period

Revenue

 

 

1193875

Kkkkkk

200902

50.171.384

 

 

1193875

Kkkkkk

202003

183.560.924

 

 

1191272

Pppppp

201702

124.391.862

 

 

1191272

Pppppp

201106

41.463.954

 

 

1191108

Rrrrrr

201311

829.279

 

 

1176547

Qqqqqq

201502

19.607.843

 

 

1176547

Qqqqqq

201810

40.542.533

 

 

1193875

Kkkkkk

200903

40.542.533

 

 

1193875

Kkkkkk

202004

287.483.414

 

 

1191272

Pppppp

201705

160.198.290

 

 

1191272

Pppppp

201106

165.855.816

 

 

1191108

Rrrrrr

201312

40.542.533

 

 

1176547

Qqqqqq

201501

202.712.664

 

 

1176547

Qqqqqq

201810

20.271.266

Link to comment
Share on other sites

One quick solution would be as below. Note this solution uses 10.x version:

1) Insert columns period and revenue matching on cust_id from both tables with left outer join

2) Add transformation to calculate new column which identifies whether period value is between start and end date. Filter out rows whose period value is not between start and end dates

2. Select Data > Add...

Source: Data table from current analysis

Data table: Data Table (3)

Update behavior: Automatic

Data loaded at: 2/11/2020 5:27 PM

Data was added as new columns in data table 'Data Table (2)'

Matching behavior: Tries to match the specified columns when data is loaded

Matched columns: Cust_ID Cust_ID

Added columns:

Period

Revenue

Ignored columns: Cust_Name

Join method: Left outer join

Treat empty values as equal: No

3. Select Data > Transform data...

Added transformations

Transformation name: Calculate new column

Column name: IncludeRows

Expression: case when ([Period]>=[sTART]) and ([Period]

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...