Skip to Content
avatar image
Former Member

BODS Merging a Date field and KPI's

Hi ,

I have a situation where I have to merge a date field which is of same format from different queries and different KPI's within those queries and load into a single table ? Is there a way we can do it .

For Example:

Query 1: Date , KPI 1

Query 2: Date, KPI 2

Query 3: Date, KPI 3

My final output in my target table should be :

Date, KPI 1, KPI 2, KPI 3

Please advise.

Thanks,
Naveen

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Nov 18, 2016 at 10:23 AM

    Isn't this a simple join?
    Query1.Date = Query2.Date and Query1.Date = Query3.Date

    Then map Date, KP1, KP2 and KP3 to the output schema.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 31, 2016 at 06:36 PM

    Please check the Data Services reference guide and the information on using the pivot transform. this takes the information based on one column that you pivot on and puts it into rows. I believe that this would do what you are looking for.

    Best Regards,

    Denise

    SAP Support

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Denise,

      This is not what I exactly want , I want to merge the date of all the different data sources into one single date with their respective fields and load into a single table .

      Thanks,

      Naveen

  • avatar image
    Former Member
    Nov 03, 2016 at 05:57 AM

    Hi Naveen,

    We can achieve this by using MERGE Transformation.

    Note : The structure should be same for all the 3 sources.

    Thanks & Regards,

    Samata B.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 18, 2016 at 09:39 AM

    Step 1: Merge, The Merge schema should be like in the target (Date, KPI 1, KPI 2, KPI 3), so introduce extra columns in your queries and map them to NULL (add KPI 2=NULL, KPI 3= NULL to the Query 1 and so on)

    Step 2: use Query with Group By Date and max or min function for each of the KPI fields

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 22, 2016 at 08:55 PM

    Yes , Its a join with a Predefined Table which contains dates , That Worked!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 05, 2017 at 02:50 PM

    Yes , Its a join .. Use a Predefined Table define in the Database which has dates and just join the whole other tables which has KPI with data column .

    Add comment
    10|10000 characters needed characters exceeded