Object based on result of a count

[DrSmyth]

I've posted a similar thread to this before, but I have not yet found a complete solution so i thought i'd go for it....

A bit of background first, I'm using BO 5.1.6 with a db2 database (version 6/type OS/390) and i'm trying to create a new object.

There are three fields involved in this new object <WillCod>, <MirrWillCod>, <PrimaryInd>

<WillCod> is unique to each item, each <WillCod> has a <MirrWillCod> and up to two items can share a <MirrWillCod>, each item also has a <PrimaryInd>. All these fields are in the same table.

If a <WillCod> has a <MirrWillCod> that also applies to another <WillCod> then these items are linked and one will have a <PrimaryInd> of 'Y' and the other will have a <PrimaryInd> of 'N'. If a <WillCod> has a unique <MirrWillCod> then it has no linked item and will have a <PrimaryInd> of 'Y'

OK, sorry about the long winded explanation but i think iit's integral to the problem. Now onto the new object i want a linked <WillCod> with <PrimaryInd> 'Y' to be labled as 'P', a linked <WillCod> with <PrimaryInd> 'N' to be 'M' and a non linked <WillCod> to be 'S'.

I've tried all sorts, mainly based around counting distinct<WillCod> for each <MirrWillCod>'s, but the problem is that evertime i include an item relating to <WillCod> the count is 1 and it BO assumes that there are no linked items...

Does anybody have any suggestions for any concepts i can try???

 

[Sridharan]

Where are you trying to accomplish the thing??? Is it at the report level or at Universe Level.

Sri

 

[blom0344]

Hard to come up with suggestions, cause it is quite hard to visualize what you are after.
With a selection from your table this will be much easier.
If you send me some data I'll give it a shot.........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

OK, here's a brief overview of what some of the data might look like

willcod MirrWillCod PrimaryInd
1 1 y
2 1 n
3 2 y
4 3 y
5 4 y
6 4 n
7 5 y
8 6 y
9 7 y
10 7 n

A quick run through of the indicator for each willcod

1 - P, 2 - m, 3 - s, 4 - s, 5 - p, 6 - m, 7 - s, 8 - s, 9 - p, 10 - m

A quick logic run through: Matching MirrWillCod has one 'P' WillCod and one 'M' WillCod (maximum of two WillCod's with same MirrWillCod in the table) single MirrWillCod are all s willcod.

T.Blom, is this what you mean by giving you some data? Have e-mailed you with an actual sample of data..

Cheers for the offer of help lads..

 

[DrSmyth]

Thanks for e-mailing a good solution to this query T.Blom, for the sake of other users thought i'd post your response in here.

The answer is two new objects at reporting level, the first one is called count1: count <willCod> in <MirrWillCod>

And the secound is an if statement called Indicator, that gives the required indicator tags

= If <Count 1>=1 Then &quot;S&quot; Else If <Count 1>=2 And <PrimaryInd>=&quot;Y&quot; Then &quot;P&quot; Else If <Count 1>=2 And <PrimaryInd>=&quot;N&quot; Then &quot;M&quot; Else &quot;U&quot;

Hey presto!!!!!