Exclude a characteristic value based on another char value

[Former Member]

Hi

I have a situation like below

country	sold to	ship to
EU	1010	160
EU	1010	161
US	1011	120
CZ	1012	150
CZ	1012	150
CZ	1012	122
CZ	1012	123
CZ	1012	125
CZ	1012	150

Now the requirement is to exclude sold to = 1012 except where ship to is = 150. Therefore i would need the result as below.Please help how could I achieve this.

country	sold to	ship to
EU	1010	160
EU	1010	161
US	1011	120
CZ	1012	150
CZ	1012	150
CZ	1012	150

Thanks in Advance

[Former Member]

These situations can occur as enhancements after the initial design , so it is normally difficult to either enhance the info provider layer, this can be due to number of rows or time consumed to reload.

Ideally I will go with concatenation at the begin of initial design , however this may not end at just the 2 objects we have, it can be some other objects( may be combination of 3 or 4). BW Query does not have the capability for such a functionality.

We can have 2 queries , one restricted on exclude soldto 1012, another include (1012,150), and combine these 2 queries in a fronted tool. BOBJ tools can have this functionality to play with 2 sources/queries.

Just to explore, here is one more.(may not work under complex aggregation scenarios)

Create 2 RKF's and 1 CKF, 1 condition

RFK1 - Include 1012,150

RKF2 - Exclude 1012

CKF1 = RKF1 + RKF2

Condition1 = CKF1 not equal to zero

This will filter out ZERO  value rows.(May be performance sensitive if you have more data in query output)

Gopi

[BernhardE]

Hi Karthik,

I think that you can realise such a query scenario using virtual characteristics.
Here you should be able to remove all rows whith soldto=1012 and shipto<>150.

Have you already tried this approach ?

BR Bernhard

[ccc_ccc]

Hi Karthik,

We can solve this problem with below options.

Approach 1.

1. Create new characteristic called ZSOLSHIP(length is sold to length + ship to length)
2. Write routine for newly crated infoobject ZSOLSHIP as like below
3. RESULT = CONCATENATE source_fields-sold_to source_fields-ship_to EX 1012150
4. Direct restrict at BEx query designer level at FILTER Area OR you can create variable give chance to user enter values

Approach 2. (this will work only if sold_to & ship_to values all are NUMERIC).

1. Create two replacement path formula variables for both sold_to and ship_to (keep both replacement path formula variables in COLUMNS)
2. Keep this both sold_to and ship_to in ROWS
3. Create new formula Ex SOL_SHP
4. Logic would be = sold_to = 1020 and ship_to = 150 * your key figure .

Approach 3.

1. Use virtual characteristics concept.

Thank you,

Nanda