[CLOSED] McGyver Technique

December 16, 2010, 05:10 PM

It has been a long time since I have used this technique and I find I may be in need of it. I have a table that I need to find the difference between the two most recent inventory dates listed for a location ID. I am posting a sample of the data. Am I thinking along the right lines here?

LOCATION_ID INVENTORY_DATE
1 6/4/2010 0:00
1 5/20/2008 0:00
1 4/13/2007 0:00
1 6/12/2009 0:00
1 3/14/2005 0:00
1 4/4/2004 0:00
1 4/20/2006 0:00
2 3/20/2008 0:00
2 4/16/2007 0:00
2 5/7/2006 0:00
2 4/12/2010 0:00
2 4/5/2009 0:00
2 5/17/2004 0:00
2 5/15/2005 0:00
3 4/21/2008 0:00
3 6/7/2007 0:00
3 7/10/2006 0:00
3 3/29/2010 0:00
3 4/26/2004 0:00
3 8/16/2004 0:00
3 3/30/2009 0:00
3 7/24/2005 0:00

This message has been edited. Last edited by: Kerry,

December 16, 2010, 05:24 PM

Waz

If I understand, you want to know the difference between the last to Investory Dates per Location Id

If so, you can easily do this without Macgyver.

e.g.

TABLE FILE source
BY LOCATION_ID BY HIGEST 2 INVENTORY_DATE
ON TABLE HOLD AS TMP_DATA
END
TABLE FILE TMP_DATA
SUM COMPUTE
    DIFF/I9 = MAX.INVENTORY_DATE - MIN.INVENTORY_DATE ;
BY LOCATION_ID
END

It could also be one in one pass, but will be more complex.

December 16, 2010, 06:22 PM

WendyC

Actually, no. Not between the max and min. The difference between dates of the two most recent inventory dates. So for example, with location #1 the two most recent dates are 6/4/2010 and 6/12/2009 - and of course they aren't in order. What about using ranked by?

December 16, 2010, 06:36 PM

j.gross

quote:
BY HIGEST 2

December 16, 2010, 06:40 PM

Waz

As Jack quite rightly pointed out my spelling mistake, BY HIGHEST 2 is the key.

Check this code out.

-* Write out a master to read the TST_DATA list
EX -LINES 5 EDAPUT MASTER,TST_DATA,CV,FILE
FILENAME=TST_DATA, SUFFIX=FIX,$
SEGNAME=TST_DATA, $
  FIELD=LOCATION_ID ,ALIAS=  ,A1 ,A2 ,$
  FIELD=INVENTORY_DATE ,ALIAS=  ,MDYY ,A10 ,$

FILEDEF TST_DATA DISK tst_data.ftm (LRECL 12

-RUN

-WRITE TST_DATA 1 06/04/2010
-WRITE TST_DATA 1 05/20/2008
-WRITE TST_DATA 1 04/13/2007
-WRITE TST_DATA 1 06/12/2009
-WRITE TST_DATA 1 03/14/2005
-WRITE TST_DATA 1 04/04/2004
-WRITE TST_DATA 1 04/20/2006
-WRITE TST_DATA 2 03/20/2008
-WRITE TST_DATA 2 04/16/2007
-WRITE TST_DATA 2 05/07/2006
-WRITE TST_DATA 2 04/12/2010
-WRITE TST_DATA 2 04/05/2009
-WRITE TST_DATA 2 05/17/2004
-WRITE TST_DATA 2 05/15/2005
-WRITE TST_DATA 3 04/21/2008
-WRITE TST_DATA 3 06/07/2007
-WRITE TST_DATA 3 07/10/2006
-WRITE TST_DATA 3 03/29/2010
-WRITE TST_DATA 3 04/26/2004
-WRITE TST_DATA 3 08/16/2004
-WRITE TST_DATA 3 03/30/2009
-WRITE TST_DATA 3 07/24/2005

TABLE FILE TST_DATA
BY LOCATION_ID BY HIGHEST 2 INVENTORY_DATE
ON TABLE HOLD AS TMP_DATA
END
TABLE FILE TMP_DATA
SUM 
    MAX.INVENTORY_DATE
    MIN.INVENTORY_DATE
    COMPUTE
    DIFF/I9 = MAX.INVENTORY_DATE - MIN.INVENTORY_DATE ;
BY LOCATION_ID
END

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!