CODEWITHSUNDEEP
oracle-databasespotfire
direwolf
direwolf

Reputation: 121

Need to Create calculated columns from input date -18 months in Spotfire for the actual columns

I need to create a report where for each columns there will be a previous column which will give the value from date 18 months back from the date that is given as input. Basically I am getting data from few columns into Spotfire for a particular date and want few of the columns to show the output that was 18 months back.

Code Summary-

This is the code i have to implement into Spotfire. This report takes input of a particular single day's date and gets column values for it. Sub-query gets for few of the values and they are sent to the main query. Typically this report has few common columns and few other columns which have the value from current date and previous dates for the same columns. I can implement all the columns from the main query, but need suggestions to get values for the previous columns calculated in Spotfire or anyway to implement as an Oracle view since we will be getting only one input for the main query and sub-query will be deducting static no of days/month[in this case its 18 months]

Code Sample:-

 select  st.x1, 
            cs.x2 , 
        sp.x3, sp.x4,     
        el.x5 current_zxc,       --New data 2
        el.xxxx current_zvvxx,   --New data 3
        por.x6 current_zczxc,    --New data 4
        el.x7 current_sdcvzv,    --New data 5

        prev_yr_data.prev_1 previous_czzxczxc,
        prev_yr_data.prev_2 previous_xcvv,
        prev_yr_data.prev_3 previous_zcvzxz,
        prev_yr_data.prev_4 PREVIOUS_czxcvzxv,
        prev_yr_data.prev_5 previous_vvvxcvxc,
        prev_yr_data.prev_6 previous_zxvxvv,


    from table1 cs 
    inner join table2 usr on cs.xxx = usr.zzzzz
    inner join table3 emp on emp.xxx = usr.zzzzz
    inner join table4 gbst on cs.xxxs = gbst.zzzzz
    inner join table5 sp on cs.xxx = sp.zzzzz
    inner join table6 st on sp.xxx = st.zzzzz 
    inner join table7 ol on ol.xxx = cs.zzzzz
    inner join table8 el on el.xxx = ol.zzzzz
    inner join table9 spt on trim(upper(el.xxxx)) = trim(upper(spt.xxx)) 
    inner join table10 por on 
       por.xxx = el.xxxx and
       por.xxxx = el.xxxx and 
       por.xxxx = cs.zzzzz
    inner join 

    (select st.x1, 
        cs.zzzzz case_zzzzz, 
        cs.x2 prev_4, 
        sp.zzzzz , 
        sp.x3, sp.x4, 
        spt.zzzzz ,
        spt.xxx prev_1,   --Old data 1
        el.x5 prev_2,  --Old data 2
        el.x6 prev_3,   --Old data 3
        por.xxxx prev_5,  --Old data 4
        el.x7 prev_6   --Old data 5
    from table1 cs 
    inner join table5 sp on cs.xxxx = sp.zzzzz
    inner join table6 st on sp.xxxx = st.zzzzz 
    inner join table7 ol on ol.xxxx = cs.zzzzz
    inner join table8 el on el.xxxxx = ol.zzzzz
    inner join table9 spt on trim(upper(el.x_part_name)) = trim(upper(spt.x_part_number)) 
    inner join table10 por on 
       por.xxx = el.xxxx and
       por.xxxx = el.xxxx and 
       por.xxxx = cs.zzzzz
    where ol.date_time between  add_months(to_date('date_input','mm/dd/yyyy'), -18) and to_date('date_input','mm/dd/yyyy') 
    ) prev_yr_data on

    sp.zzzzz = prev_yr_data.zzzzz and 
    spt.zzzzz  = prev_yr_data.zzzzzz
    where ol.date_time >=  to_date('date_input','mm/dd/yyyy') and ol.date_time  < ( to_date('date_input','mm/dd/yyyy')  + 1 )

Upvotes: 1

Views: 511

Answers (1)

TxAggie2007
TxAggie2007

Reputation: 21

I would suggest adding a transformation when you bring in the data set to calculate your date 18 months in the future (or past whichever you prefer). Then you can do a self join within Spotfire where [Date] = [18MonthsForward] and bring in the same value column(s) that you desire.

The steps to achieve this look like this when viewed under source information:

2. Select Insert > Transformations...
    Add the transformations:
        a. Calculate new column
            Name: 18MonthsForward
            Expression: DateAdd("month",18,[Date])

3. Select Insert > Columns...
        Select 'From Current Analysis'
        Source location: Data Table
        Automatic update.
        Match columns:
            Date => 18MonthsForward
        Ignore columns:
            Date
        Select join method: LeftOuterJoin
        Treat empty values as equal: False

As a step by step instruction you would:

(1) Select Insert >> Transformation

(2) Select Calculation new column from the drop down

(3) Type the expression: DateAdd("month",18,[Date]) and name this whatever you prefer

(4) Select Insert >> Columns

(5) Select from current data and select your current data table

(6) Join on Date and your newly calculated 18MonthsForward date column

(7) Select your value column as the new column

Attached in an image of the data resultant data table. 18 months prior value

Upvotes: 1

Related Questions