WEBI 4.1 - merge line items

[Former Member]

Hi,

I have 2 BEx queries.

Query 1

Order Item Date1

111     10   20150101

111     20   20150303

111     30   20150602

Query 2

Order Item Date2

111     10   20150507

111     20   20150403

What I would like to achieve:

Order Item   Date1       Date2

111     10     20150101 20150507

111      20    20150303 20150403

111      30    20150602

I can merge the orderno, but that results in item referencing problems.

I tried to create 2 variables and concatenate order and item. However merging variables is a no-go area.

We use BO 4.1 support pack 5.

As said before no universe involved, both are BEx queries.

I am not able to create the concatenations in the SAP backend.

Many thanks!

[Former Member · Accepted solution]

Have you tried merging Item as well?

Then have Date1 as =max([Query 1].[Date 1]) and Date 2 as =max([Query 2].[Date 2]) as your two date columns

[Former Member · Accepted solution]

Hi Mark,

Yes I did try that. Also with all kinds of where clauses. But that didn't work for me.

Was not able to properly join the item numbers.

regards marcel

[Former Member · Accepted solution]

Hi Mark,

I have tried your suggestion and it seems to work . Turn date into measure and user max(date) was the missing thing.

Thx!

regards marcel

[Former Member]

What all are your dimensions out of these? And if both Order and Quantity are dimensions, are you merging both or are you merging item only?

-Ankush

[Former Member]

Quantity you are referring to is order item number.

All are dimensions. I have tried merge on order header only, and merge on both order header and order item.

However, no success. As far as I know there is no nesting option or AND option available when you merge dimensions.

[Former Member]

Well than you can do the following:

1. You can merge both the dimensions.

2. Create new detail type variables, associate both them to the [Item] and let them be equal to =[date1] and =[date2]

3. Now you can use the merged dimensions along with the detail variables in a block to run them.

Sorry I am late as I was confirming if it works.

-Ankush