Data Prep "Ditto"

Hi @Janet Grimsley,

I've seen @Stephen Smay  do some really cool things in this arena with using appends and sorts. @Stephen Smay what do you think?


------------------------------
Baba Majekodunmi
Solutions Consultant
Altair Engineering Inc.
Manassas VA
978-275-9325
------------------------------
-------------------------------------------
Original Message:
Sent: 04-20-2020 04:53 PM
From: Janet Grimsley
Subject: Data Prep "Ditto"

Is the Ditto function in Data Prep only able to fill down?  I have a situation in which I would like to fill "UP".

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
"

Short answer is yes.  Ditto will only fill in blanks with a previous value.

One of the easiest workarounds would be to add a column that contains the row number and then do a reverse sort on that column and then apply the ditto function.

There is currently an enhancement request to add directional control (up or down) to the ditto function and is currently targeted for version 16.1 of Data Prep Studio but I do not have a date available and is always subject to change.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 04-20-2020 04:53 PM
From: Janet Grimsley
Subject: Data Prep "Ditto"

Is the Ditto function in Data Prep only able to fill down?  I have a situation in which I would like to fill "UP".

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
"

*Edit - Sorry Chris, I didn't see your reply!


Hi Janet,

The big difference between Ditto in Classic and Data Prep is that Data Prep Studio also has it as a table function.

It requires a few steps, so if it can be done with Footers and 'cleared by' templates, then that would probably be best.  However . . . . 

Step 1.  Don't use the option in the report extraction, you'll get something like this . . . 
Step 2 : Right click anywhere and select Column->New Row Number Column
Step 3 : Transform -> Sort on descending Row Number
Step 4 : Right Click the Value column and select Replace->Ditto
Step 5 : Optionally re-sort on ascending Row number and delete the row number column

Regards,
Steve.

------------------------------
Steve Caiels
Professional Services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 04-20-2020 04:53 PM
From: Janet Grimsley
Subject: Data Prep "Ditto"

Is the Ditto function in Data Prep only able to fill down?  I have a situation in which I would like to fill "UP".

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------"

Short answer is yes.  Ditto will only fill in blanks with a previous value.

One of the easiest workarounds would be to add a column that contains the row number and then do a reverse sort on that column and then apply the ditto function.

There is currently an enhancement request to add directional control (up or down) to the ditto function and is currently targeted for version 16.1 of Data Prep Studio but I do not have a date available and is always subject to change.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 04-20-2020 04:53 PM
From: Janet Grimsley
Subject: Data Prep "Ditto"

Is the Ditto function in Data Prep only able to fill down?  I have a situation in which I would like to fill "UP".

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
"

I like your thought process Chris!  Thanks for the concept!

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
-------------------------------------------
Original Message:
Sent: 04-21-2020 06:54 AM
From: Chris Porthouse
Subject: Data Prep "Ditto"

Short answer is yes.  Ditto will only fill in blanks with a previous value.

One of the easiest workarounds would be to add a column that contains the row number and then do a reverse sort on that column and then apply the ditto function.

There is currently an enhancement request to add directional control (up or down) to the ditto function and is currently targeted for version 16.1 of Data Prep Studio but I do not have a date available and is always subject to change.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------

Original Message:
Sent: 04-20-2020 04:53 PM
From: Janet Grimsley
Subject: Data Prep "Ditto"

Is the Ditto function in Data Prep only able to fill down?  I have a situation in which I would like to fill "UP".

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
"

I like your thought process Chris!  Thanks for the concept!

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
-------------------------------------------
Original Message:
Sent: 04-21-2020 06:54 AM
From: Chris Porthouse
Subject: Data Prep "Ditto"

Short answer is yes.  Ditto will only fill in blanks with a previous value.

One of the easiest workarounds would be to add a column that contains the row number and then do a reverse sort on that column and then apply the ditto function.

There is currently an enhancement request to add directional control (up or down) to the ditto function and is currently targeted for version 16.1 of Data Prep Studio but I do not have a date available and is always subject to change.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------

Original Message:
Sent: 04-20-2020 04:53 PM
From: Janet Grimsley
Subject: Data Prep "Ditto"

Is the Ditto function in Data Prep only able to fill down?  I have a situation in which I would like to fill "UP".

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
"

Steve, wow, wow, wow.....love the "out of the box" thinking.  I should have thought of that.  Thanks!

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
-------------------------------------------
Original Message:
Sent: 04-21-2020 07:09 AM
From: Janet Grimsley
Subject: Data Prep "Ditto"

I like your thought process Chris!  Thanks for the concept!

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------

Original Message:
Sent: 04-21-2020 06:54 AM
From: Chris Porthouse
Subject: Data Prep "Ditto"

Short answer is yes.  Ditto will only fill in blanks with a previous value.

One of the easiest workarounds would be to add a column that contains the row number and then do a reverse sort on that column and then apply the ditto function.

There is currently an enhancement request to add directional control (up or down) to the ditto function and is currently targeted for version 16.1 of Data Prep Studio but I do not have a date available and is always subject to change.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair

Original Message:
Sent: 04-20-2020 04:53 PM
From: Janet Grimsley
Subject: Data Prep "Ditto"

Is the Ditto function in Data Prep only able to fill down?  I have a situation in which I would like to fill "UP".

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
"

Hi Janet,
There is no fill up function in DPS or Classic.  You may use the recno(), rowno() functions along with the sort to fill up empty cells.
It is not a straight forward technique, but might work in certain situations.


Regards

Mo

------------------------------
Mahmoud Abdolrahim
Senior Implementation & Integration Engineer
Datawatch Corporation
MA
(978) 935-3840
------------------------------
-------------------------------------------
Original Message:
Sent: 04-20-2020 04:53 PM
From: Janet Grimsley
Subject: Data Prep "Ditto"

Is the Ditto function in Data Prep only able to fill down?  I have a situation in which I would like to fill "UP".

------------------------------
Janet Grimsley
VP, Business Intelligence
The Fauquier Bank
Warrenton VA
(540) 349-0215
------------------------------
"