Create incremental IDID as group 4

[TariqMehmod]

Sir I have these codes


[pre]
CREATE CURSOR junk(qty n(3),idid n(3))

INSERT INTO junk values(5,1)
INSERT INTO junk values(10,0)
INSERT INTO junk values(15,0)
INSERT INTO junk values(20,0)
INSERT INTO junk values(20,0)
INSERT INTO junk values(20,0)
INSERT INTO junk values(15,0)
INSERT INTO junk values(10,0)
INSERT INTO junk values(5,0)
INSERT INTO junk values(0,0)
INSERT INTO junk values(10,0)
INSERT INTO junk values(15,0)
INSERT INTO junk values(20,0)
INSERT INTO junk values(20,0)
INSERT INTO junk values(20,0)
INSERT INTO junk values(15,0)
INSERT INTO junk values(10,0)
INSERT INTO junk values(5,0)
INSERT INTO junk values(10,0)
INSERT INTO junk values(15,0)
INSERT INTO junk values(20,0)
INSERT INTO junk values(20,0)
INSERT INTO junk values(20,0)
INSERT INTO junk values(15,0)
INSERT INTO junk values(10,0)
INSERT INTO junk values(5,0)
[/pre]

I want to fill idid column as follows

Logic:

Qty increase then decrease in every idid group.
New idid starts from small to bigger number like shown with red color.

Please

 

[Tore Bleken]

Posting the same question in several forums is nagging. Be patient....!

 

[Mike Lewis]

What exactly is your criterion for the change of group? It's not that the group changes each time that the quantity increases. And it's not that it changes when the quantity reaches a particular value such as 0 or 5.

If you could state the rule, as precisely as possible, it will be easier to devise a method.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

Mike: think of the data as a series of "waves", which he wants to count when they start.

 

[Mike Lewis]

Thanks, Tore. In other words, we have a series that rises and falls repetitively. When it changes from a fall to a rise, that's when we have a new group. That makes perfect sense.

On that basis, Tariq, I would think you need something like this:

lnID = 1
lnPrevQty = 0
llAscending = .T.
SELECT Junk
SCAN
  IF llAscending AND Qty <= lnPrevQty
      llAscending = .F.
  ENDIF 
  IF NOT llAscending AND Qty > lnPrevQty
        llAscending = .T.
        lnID = lnID + 1 
   ENDIF 
   REPLACE IDID WITH lnID
   lnPrevQty = Qty
ENDSCAN

I've only given this a quick test, so cannot guarantee that it will always work in all cases. It should give you a starting point, but if you do use this code, be sure to test it with various combinations of data.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TariqMehmod]

Sir,
In attached image, there are 3 different groups with different colors.

Every groups starts with small number and raises then again small number.

Please forgive me, May I could not narrate proper requirement but attaced image is corrrct to display what I need.

Please

 

[Mike Lewis]

That's OK. Tore explained it quite well. Did you have a chance to try my code?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

What do you want to know? How you would compute the idid from the data if it wasn't set as given by your test data? Or how to compute Qty value waves, if the idid is given?

If Qty is abouit stock quantity there would be beter ways to detect "waves", simply incrementing idid if new stock arrives from your suppliers.

If the qty does not always change that smoothly but for example goes from 0,5,10,20,15,20,15,10,0,5,... you would need to work with thresholds to avoid counting such small fluctuations as "waves".

So overall this needs more specific rules to be programmed.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TariqMehmod]

Thanks Sir Mike
Your codes work fine.
How to rate you?

 

[Mike Lewis]

How to rate you?

I think you already have. Unlike some forums, Tek-Tips doesn't have a way of rating a post - in the sense of assigning a score to it. But we do have a way of flagging a "great post". This cause a red star to appear against the post, which is useful as it indicates that the original problem has been solved. The star also appears against the thread in the main forum page, and this helpful to other forum members who might have the same problem.

In this particular case, three stars have already been given in this thread - either by yourself or someone else.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TariqMehmod]

Sir Mike

Thank you very much for helping.

But now I need your more kind help

I have added a new field called max_qty where I want to put 1 against maximum qty in every idid group.
Like shown in attached image in red colors.

Please help

 

[wibenji]

Try adding this after Mike´s Code

FOR I=1 TO LNID
  GNMAXIMUM = 0  && Initialize minimum value
  SCAN FOR JUNK.IDID=I
    GNMAXIMUM = MAX(GNMAXIMUM, JUNK.QTY)
  ENDSCAN
  LOCATE FOR JUNK.QTY=GNMAXIMUM AND JUNK.IDID=I
  REPLACE JUNK.MAX_QTY WITH 1
  GNMAXIMUM = 0
ENDFOR