New Value Indicator 1

[nagornyi]

In one of the threads of this forum an interesting problem came up. Given a sequence of records, for example

Field
=====
  A
  A
  A
  B
  A
  C
  D
  B

we need to add a column of indicators that would assume 1 every time a new value happens in the Field, otherwise it will be 0. That is

Field  Indicator
=====  =========
  A       1
  A       0
  A       0
  B       1
  A       0
  C       1
  D       1
  B       0

I suggested the following solution.
We add two running totals fornulae: RT1 and RT2. The first one would count the records distinct, the second one would just count the records:
That is

Field  RT1  RT2
=====  ===  ===
  A     1    1
  A     1    2
  A     1    3
  B     2    4
  A     2    5
  C     3    6
  D     4    7
  B     4    8

The indicator should assume 1 every time the RT1 changes its value. As the

Previous

fumction is not working for running totals, I simulated the function with the following formula:

//@Indicator
whileprintingrecords;
Global NumberVar T;
NumberVar P;
if {#RT2}>1 then P:={#RT1}-T else P:=1;
T:={#RT1};
P;

In the formula the global variable T keeps the previous value of RT1, while P assumes the difference between current and previous values of RT1. The RT2 is needed only to process the first record, for which there is no previous, and indicator is always 1 on the first record.

Field  RT1  RT2  Indicator
=====  ===  ===  =========
  A     1    1       1
  A     1    2       0
  A     1    3       0
  B     2    4       1
  A     2    5       0
  C     3    6       1
  D     4    7       1
  B     4    8       0

For some reason this solution which works fine on all my tests is not workig for the guy who posted the original problem.
Why this may happen?
Also: what are other ways of building the Indicator?
All comments appreciated.

 

[Madawc]

In Crystal 8.5, you could have a running total that would check if field = (previous)field and add 1 when this was not so.

Madawc Williams
East Anglia, Great Britain

 

[nagornyi]

That would be count on change of field, right?
Such a running total creates the following column:

Field  RT3  
=====  ===  
  A     1    
  A     1    
  A     1    
  B     2    
  A     3    
  C     4    
  D     5    
  B     6

I am not sure how to proceed from here to get the New Value Indicator column. Could you please advise?

 

[Ngolem]

you really didn't represent the problem well. That interesting previous post had a number of Groups involved.

You make no reference to this post....hence it is not possible for others to diagnoze the problem.

In my opinion if the above data were a single grouping, you are not attacking the problem correctly using running totals. It may work but it is far too complex.

All you have to do is create an array in the group header

//@InitComparisonArray (suppressed in Group header)

WhilePrintingRecords;

if not inRepeatedGroupHeader then
stringVar array test := ["","","","","",..add more elements 50% more than no. of current unique fields expected];
NumberVar pointer := 0;

In the detail line place this formula

//@DisplayIndicator

WhilePrintingRecords;

if not inRepeatedGroupHeader then
stringVar array test ;
NumberVar pointer ;
numberVar indicator;

if not({table.field} in test) then
(
pointer := pointer + 1;
test[pointer] := {Table.field} //or totext({Table.field} if a number
indicator := 1;
)
else
indicator := 0;

indicator;


The above formula could also be used in a conditional suppress in the section expert instead of displaying an indicator for counting...rather than do any more processing to eliminate unwanted records.




Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[nagornyi]

Thank you Jim.
This thread is not representing original problem (for those interested it is thread767-737986) and I am not intending to discuss THAT problem. I am posting a new problem that I encounted trying to solve the original one.
As for the solution, I like it, as it gives an interesting examole of using arrays. However, the need to know in advance the max possible number of unique fields and manually create for them places in array is, to my mind, a fundamental restriction of the approach.
Other ideas anyone?

 

[synapsevampire]

The indicator shouldn't be used as the field to sum, just to display, so use:

if onfirstrecord or previous({field}) <> {field} then
1
else
0

A conventional distinct count will work to sum the unique rows if they also need that.

What else did they need?

-k

 

[nagornyi]

This would work only if the records are ordered, which, unfortunately, is not the case here. For the above example

Field  we get  we need
=====  ======  =======
  A      1       1
  A      0       0
  A      0       0
  B      1       1
  A      1       0
  C      1       1
  D      1       1
  B      1       0

 

[synapsevampire]

Select Report->sort records and order them...

-k

 

[nagornyi]

This column is actually a part of the recordset, and the records are ranged by another field. It should be taken as part of the challenge that the records can not be either ranged or grouped. They just go as they go and we need to put 1 every time a new value occurs.

 

[Ngolem]

As far as the array size is concerned you could set it to 1000 elements if you wish...if you have more than that there may be a problem...but then you could fill 2 arrays then....you would probably have trouble with running totals with that total anyway.

Depending on your version I think you could use &quot;redim preserve&quot; to dynamically resize the array....but I haven't had to do that.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lbass]

Nagornyi,

Jim's solution works perfectly (except, Jim, those &quot;If not inrepeatedgroupheaders then&quot; require an &quot;else&quot;--I just eliminated them for testing). Initializing the array with hundreds of elements is simple when you copy and paste multiple lines.

Another method you could use if you were literally working with fields that were lettered from &quot;A&quot; to &quot;Z&quot; (or if there was a limited range of values for this field) would be to create a counter for each letter, as in:

whileprintingrecords;
if {table.field} = &quot;A&quot; then counterA := counterA + 1;
if {table.field} = &quot;B&quot; then counterB := counterB + 1; etc.//

Then add some clauses to the same formula to assign the 1 or 0 as in:

if {table.field} = &quot;A&quot; and counterA = 1 then 1 else
if {table.field} = &quot;B&quot; and counterB = 1 then 1 else 0

But this is the long way around--essentially creating a running total for each value of {table.field} and I'm guessing that your &quot;real&quot; {table.field} might have multiple characters that create many unique values.

-LB

 

[nagornyi]

Thank you lbass. That's also a possibility, though with more restrictions. Not only we need to know how many distinct values can be there, but also what they are.
Looks like the problem is not as simple as it seems to be. Thanks everyone who tried to solve the problem and please update the thread if any new ideas come up.

 

[synapsevampire]

No idea what &quot;ranged&quot; means, I spoke of sortation, not grouping.

At least try it first...

-k

 

[nagornyi]

Source: m-w.com

Main Entry: range
Function: verb
Inflected Form(s): ranged; rang·ing
Etymology: Middle English, from Middle French ranger, from Old French rengier, from renc, reng line, place, row -- more at RANK
Date: 14th century
transitive senses
1 a : to set in a row or in the proper order b : to place among others in a position or situation c : to assign to a category
....

Now, could you please kindly explain what the sortation means?

 

[Ngolem]

lbass - thanks for the endorsement

&quot;Jim's solution works perfectly (except, Jim, those &quot;If not inrepeatedgroupheaders then&quot; require an &quot;else&quot;--I just eliminated them for testing). Initializing the array with hundreds of elements is simple when you copy and paste multiple lines.&quot;

I am not sure why I need and &quot;else&quot; in that formula. I just want to reset the array if there is a new group header....otherwise leave it alone.

I see I did make a mistake due to cloning if this is what you were refering to

WhilePrintingRecords;

if not inRepeatedGroupHeader then
stringVar array test ;
NumberVar pointer ;
numberVar indicator;

That line doesn't belong.

And yes, copy and pasting the array nulls is quick and simple. Actually since I use this technique often I have a notepad file with this formula set out with all the null's
and just copy/paste it into my report, changing the name of the variable.

Thanks again for the support



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Ngolem]

Rats!!! there is another mistake

//@InitComparisonArray (suppressed in Group header)

WhilePrintingRecords;

if not inRepeatedGroupHeader then
stringVar array test := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,..add more elements 50% more than no. of current unique fields expected];
NumberVar pointer := 0;

should be

//@InitComparisonArray (suppressed in Group header)

WhilePrintingRecords;

if not inRepeatedGroupHeader then
(
stringVar array test := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,..add more elements 50% more than no. of current unique fields expected];
NumberVar pointer := 0;
);

I don't want the pointer reset prematurely either.




Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[nagornyi]

Thank you guys for taking active part in this discussion. Array techniques were not in my aresenal, now I see how useful they can be. Always something to learn: that's why I love reporting.

 

[lbass]

Jim,

When I tested it, your initialization formula gave a message &quot;The word else is missing,&quot; so I added &quot;else test := test;&quot; and then it was okay. Same message with the detail formula, and I just built it into the first clause instead, as in:

if not inrepeatedgroupheader and
not({table.field} in test) then
(
//etc.

Then it worked perfectly.

-LB

 

[Ngolem]

interesting...I wonder why? I haven't had that happen before

In kicking out of a For loop I always use a boolean if-then


If flag then Exit For;

Never had a problem with that...you don't always need an &quot;else&quot;....curious

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Ngolem]

lbas- I think I know what the problem was....the formula should have been

//@InitComparisonArray (suppressed in Group header)

WhilePrintingRecords;

if not inRepeatedGroupHeader then
(
stringVar array test := [&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,&quot;&quot;,..add more elements 50% more than no. of current unique fields expected];
NumberVar pointer := 0;
);
&quot;&quot;;

I think the formula needs the &quot;&quot;; at the end, to do something with the formula...that is why it is looking for the &quot;Else&quot; condition....essentially the formula was incomplete.

I sort assumed the result would be a null without it but I guess that isn't the case....funny you would have thought I'd have run into that long ago.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!