Create values for analogous periods in the same table with DS

[Former Member]

Hi,

in the course of a pilot we want to create additional columns with data for analogous periods.

We have values for a month and need to create new columns for values for last month and last year in order to compare.

Attached youu2019ll find a few sample rows resembling the basic concept of what we have

Region Country Year Month Value

Europe Spain 2009 1 285

Europe Spain 2009 2 129

Europe Spain 2009 3 153

Europe Spain 2008 1 288

Europe Spain 2008 2 284

Europe Spain 2008 3 125

Europe France 2009 1 135

Europe France 2009 2 125

Europe France 2009 3 284

Europe France 2008 1 208

Europe France 2008 2 221

Europe France 2008 3 220

In this case, new columns would be

LastMonth (125 for Spain month 1 year 2009, since this is the value for month 3 year 2008)

LastYear (288 for Spain month 1 year 2009, since this is the value for month 1 year 2008).

Can you help on what function to use in the DS query transformation?

thanks everybody, and enjoy!

[Former Member · Accepted solution]

Hi David,

Probably the easiest way of resolving this would be to add extra columns which show the projected date each row should be shown on e.g.

Region Country Year Month Value LM_Year LM_Month LY_Year LY_Month

Europe France 2008 1 208 2008 2 2009 1

Europe France 2008 2 221 2008 3 2009 2

Europe France 2008 3 220 2008 4 2009 3

Europe France 2009 1 135 2009 2 2010 1

Europe France 2009 2 125 2009 3 2010 2

Europe France 2009 3 284 2009 4 2010 3

This would be written to a staging table then used as a source with the original source file to produce the target table. I've illustrated an example of how this could be achieved below:

Create two dataflows; DF 1 and 2.

In DF 1 read source, add additional columns to show the date (year, month, etc) this should be projected as. This would need to be done for both the last month and last year. Load staging table.

In DF 2 Read the source and outer join to the new staging table twice. Use the region, Country, Year, Month, to join and use the new additional projected date columns. Ensure the last month date fields are used on one staging table and last year date fields are used on the next staging table. The value from the two staging tables is then used in the last month and last year value.

Load into target table.

To calculate the projected date columns you would probably want to format the source fields into a date then add the required number of months. e.g. to_date('01' || month || year, 'DDMMYYYY') + 12

Obviously this would need the stage table to be truncated before each load and you would need to assess your load strategy. When doing these calculated fields if you have any retrospective updates on the source then these would then need recalculating, therefore this assumes it's a complete reload each time.

Edited by: Richard Saltern on Jul 16, 2010 2:01 PM

Edited by: Richard Saltern on Jul 19, 2010 10:10 AM