Skip to Content
0

BODS Merging a Date field and KPI's

Oct 31, 2016 at 03:45 PM

150

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Dirk Venken
Nov 18, 2016 at 10:23 AM
1

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Dirk,

Wouldn't merge + group by perform better than joins on large data sets? I'm sure it would in SQL, but I'm not sure about the Job Server processing.

0
  1. Functionally this is a join
  2. OP didn't include any volume information
  3. The join can be pushed down to the database. The merge cannot (except to HANA)
2
Denise Meyer
Oct 31, 2016 at 06:36 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
Samata Beesabattula Nov 03, 2016 at 05:57 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Andrey Surinov
Nov 18, 2016 at 09:39 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Bala Naveen Chilla Nov 22, 2016 at 08:55 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Bala Naveen Chilla Apr 05, 2017 at 02:50 PM
0

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 .

Share
10 |10000 characters needed characters left characters exceeded