Matrix

[Bostjan Ahacic]

Hi,

I do not know how to solve the following problem.

The product is manufactured in the following order (Table 1):

Table 1:

 

 

 

Date

Product code

 

 

1.01.2020

G

 

 

2.01.2020

B

 

 

3.01.2020

A

 

 

4.01.2020

A

 

 

5.01.2020

D

 

 

6.01.2020

D

 

 

 

After the production of a certain product (A, B, C,...) is finished and before the new production begins, a set-up ismade.

The prescribed Set-up time between products isdefined in Set-up matrix table (Table 2)

Table 2

 

 

 

Product

code

A

B

C

D

E

F

G

 

 

A

0,17

2,00

2,00

2,00

2,00

2,00

2,00

 

 

B

2,00

0,17

2,00

2,00

2,00

2,00

2,00

 

 

C

2,00

2,00

0,17

2,00

2,00

2,00

2,00

 

 

D

2,00

2,00

2,00

0,17

2,00

2,00

2,00

 

 

E

2,00

2,00

2,00

2,00

0,17

0,17

2,00

 

 

F

2,00

2,00

2,00

2,00

0,17

0,17

2,00

 

 

G

2,00

2,00

2,00

2,00

2,00

2,00

0,17

 

 

 

Now I would like to combine in Table 1 correct set-up times from table 2.

I should get the following result:

 

 

 

Date

Product code

Set-up time

 

 

1.01.2020

G

/

 

 

2.01.2020

B

2,000

 

 

3.01.2020

A

2,000

 

 

4.01.2020

A

0,170

 

 

5.01.2020

D

2,000

 

 

6.01.2020

D

0,170

 

 

 

An idea how to do that

Best regards,

Botjan

[Khushboo Rabadia]

First import table 1 in spotfire, add calculate new value through transformation, which will get previous date product code

2. Select Data > Transform data...

Added transformations

Transformation name: Calculate new column

Column name: prev

Expression: First([Product code]) OVER (Previous([Date]))

Then while importing table 2, addunpivot transformation

2. Select Data > Transform data...

Added transformations

Transformation name: Unpivot

Columns to pass through: Product Code

Columns to transform:

A

B

C

D

E

F

G

Category column name: Category

Category column data type: String

Value column name: Value

Value column data type: String

Empty values included: YesThen add columns to table1 from table2, matching on product code and previous product code

3. Select Data > Add...

Source: Data table from current analysis

Data table: Data Table (2)

Update behavior: Automatic

Data loaded at: 2/12/2020 3:34 PM

Data was added as new columns in data table 'Data Table'

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

Matched columns:

Product code Product Code

prev Category

Added columns: Value

Ignored columns: (None)

Join method: Left outer join

Treat empty values as equal: No

This would give you the required table structure.