Generate Rows to Infill Dates

[Mike Sax 2]

I have a dataset that records a product's sale amount and sale date. If there is no sale, there is no data entry so the data looks like Table 1 below. I need to create a table in Spotfire that generates rows to fill in missing dates for a set date range like in Table 2. I was unable to find an easy way to do this in Spotfire but I would be appreciative for any leads on how to do this.

Thank you,

Mike

 

Table 1

 

 

 

Product Type

Sale Date

Sale Amount

 

 

A

1/1/2020

$10

 

 

A

1/5/2020

$12

 

 

A

1/7/2020

$5

 

 

B

1/5/2020

$7

 

 

B

1/6/2020

$2

 

 

B

1/8/2020

$8

 

 

B

1/9/2020

$4

 

 

C

1/2/2020

$7

 

 

C

1/5/2020

$2

 

 

C

1/6/2020

$1

 

 

 

 

Table 2:

 

 

 

Product Type

Sale Date

Sale Amount

 

 

A

1/1/2020

$10

 

 

A

1/2/2020

$0

 

 

A

1/3/2020

$0

 

 

A

1/4/2020

$0

 

 

A

1/5/2020

$12

 

 

A

1/6/2020

$0

 

 

A

1/7/2020

$5

 

 

A

1/8/2020

$0

 

 

A

1/9/2020

$0

 

 

A

1/10/2020

$0

 

 

A

1/11/2020

$0

 

 

A

1/12/2020

$0

 

 

A

1/13/2020

$0

 

 

B

1/1/2020

$0

 

 

B

1/2/2020

$0

 

 

B

1/3/2020

$0

 

 

B

1/4/2020

$0

 

 

B

1/5/2020

$7

 

 

B

1/6/2020

$2

 

 

B

1/7/2020

$0

 

 

B

1/8/2020

$8

 

 

B

1/9/2020

$4

 

 

B

1/10/2020

$0

 

 

B

1/11/2020

$0

 

 

B

1/12/2020

$0

 

 

B

1/13/2020

$0

 

 

C

1/1/2020

$0

 

 

C

1/2/2020

$7

 

 

C

1/3/2020

$0

 

 

C

1/4/2020

$0

 

 

C

1/5/2020

$2

 

 

C

1/6/2020

$1

 

 

C

1/7/2020

$0

 

 

C

1/8/2020

$0

 

 

C

1/9/2020

$0

 

 

C

1/10/2020

$0

 

 

C

1/11/2020

$0

 

 

C

1/12/2020

$0

 

 

C

1/13/2020

$0

[Fabian Duerr]

You can use a right outer join of a table that has product type, continuous dates and an AMOUNTcolumn with 0$. Then sum your Sale Amount column and the AMOUNT column to get the new Sale Amount column. You can now drop the AMOUNTcolumn and your old Sale Amount column.

 

You could either use a data function for the table creation, the join and the creation of the new column. Or you import your table from .xls or .csv file and then continue with some transformation in Spotfire (right outer join, calculate column, remove columns)

[Mike Sax 2]

Thank you for the reply.

 

The struggle is I am trying to avoid creating a separate table with product type for all continuous dates because I'll have to repeat this analysis monthly.  It would be a much simpler task if that table existed.  I can easily create a list of continuous dates in Excel but can't seem to import them in a way where Spotfire will pair those dates to all product types.  Any additional thoughts for this nuance 

 

Thank you,

 

Mike

[Fabian Duerr]

If you go with the data function solution you won't have any troubles.The data function can extract all product types and the date range automatically from your input table. The function could run on demand or automatically whenever you open the analysis. No need for any repeated manual work. Have you ever worked with data functions

One work arund for the excel solution... Have two tables. One with the continuous dates, one with all unique product catogories and amount = 0$. In both tables you add a common column with one unique string "A". This string will be the key for a join of the two tables. (You can use any join in this case).This will result in cross join which is not availabe in Spotfire. Now you have the full table with all product types and all dates that you can join (right outer join) to your original table.

[Mike Sax 2]

I've worked with template data functions I've found through TIBCO Community but haven't had the experience of building one from scratch.  How would I build this with a data function

 

Thank you,

 

Mike

[Fabian Duerr]

Please check my little tutorial that I posted here (scroll to the end of the page):

 

https://community.spotfire.com/questions/self-referential-calculated-column

 

I hope you'll get a better understanding how to work with data functions. But you'll need some Python or R skills to get the work done ;). But in return you get the full functionality of Python/R.  This can get you way beyond basic Spotfire features.

[Ashley Krall 2]

Hi @Fabian Duerr - the link you posted is broken. Is there any chance you could re-share? Very interested in this solution!

[David Boot-Olazabal]

Hi Ashley,

I think this is the proper link: 

Within that article, there is another reference which also is broken. That link should be this one: https://community.spotfire.com/forums/topic/10176-how-to-use-the-value-of-the-n-th-row-of-a-calculated-column-within-the-calculated-column-itself-at-row-n1/#comment-10176.

 

Kind regards,

David