Hey all,

So, I have a request that is pulling back the top 5 performing cashiers by avg customers per hour for a given week (sorted by week end date), and then storing them for use later. The thing is I'm getting back multiple entries for a particular cashier (because with my request I do genuinely want the top averages). For example, let's say a cashier named Katie worked 3 times this week, and managed to get a higher average all 3 times she cashiered this week than the rest of the cashiers across the whole store or whatever. So, my request has Katie for the top 3 avg customers per hour slots and then 2 other cashiers with their avgs for the 4th and 5th highest avg slots. Below is my request. Is there a way I can alter it to only get one entry per cashier (their best avg), instead of multiple entries for the same cashier? I'm having troubles...lol

-SET &THREEWKSPREV = AYMD(&WEEKENDING.QUOTEDSTRING, -21, 'YYMD');

DEFINE FILE SRPROD
WKEND_MDYY/MDYY=DATECVT(SRPROD.DIMDATE.WEEKENDING, 'YYMD', 'MDYY');
INT_CASHIERNO/I10 = EDIT(SRPROD.SRPROD.SRPDCASHIERNO);
INT_STORENO/I5 = IF INT_CASHIERNO GT 9999 THEN INT_CASHIERNO;
STORENO/A5 = EDIT(INT_STORENO);
END
-RUN
TABLE FILE SRPROD
SUM
     SRPROD.DIMSTORE.DSNAME/A50
	 SRPROD.SRPROD.SRPDAVGCUSTPHR
BY HIGHEST 5 SRPROD.SRPROD.SRPDAVGCUSTPHR NOPRINT
BY SRPROD.SRPROD.SRPDCASHIERNAME/A35
BY INT_CASHIERNO
&select.EVAL
WHERE ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING');
WHERE SRPDCASHIERNO NE STORENO;
WHERE SRPDCASHIERNO NE '800';
WHERE SRPDCASHIERNO NE '997';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE FORMAT ALPHA
END
-RUN

Thanks in advance for any and all help anyone here is willing to provide. Appreciate you all.

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

You could try this:

TABLE FILE SRPROD
 SUM
  SRPROD.DIMSTORE.DSNAME/A50
  MAX.SRPROD.SRPROD.SRPDAVGCUSTPHR
 BY TOTAL HIGHEST 5 SRPROD.SRPROD.SRPDAVGCUSTPHR NOPRINT
 BY SRPROD.SRPROD.SRPDCASHIERNAME/A35
 BY INT_CASHIERNO
&select.EVAL
 WHERE ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING');
 WHERE SRPDCASHIERNO NE STORENO;
 WHERE SRPDCASHIERNO NE '800';
 WHERE SRPDCASHIERNO NE '997';
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE SAVE FORMAT ALPHA
END

Dan,

Thanks for the post and input, but that doesn't work. I did have that originally, but it sums all the times the cashier worked that week together giving incorrect values. What happens/example of using BY TOTAL: if the cashier Katie worked 3 times a particular week, and she had averaged 123 custs/hr, 150 custs/hr, and 100 custs/hr for each of the times she cashiered, if I used BY TOTAL it would give me the sum of those which is 373. Katie didn't have a total avg of 373 custs/hr because each value is already an average. Also, I want to get the highest single avg for a particular cashier once only. So, for this scenario I would want to be getting back 150 custs/hr for Katie.

Thanks though. Any other ideas? Anyone?

It seems to me that you will have to use 3 steps. the first step will extract all the averages by highest average by cashier. The second step will take only the highest average for each cashier. The third step will then get top 5 performing cashiers.

RSquared,

So the 3 steps/requests you are saying to make would be:

1. The request I've already created, save get rid of the "5" part?
2. A 2nd request based on request 1 that gets the highest for each cashier?
3. A 3rd request based on request 2 that gets the highest 5 from the batch?

That sounds like it could work. I just can't wrap my head around how I would write request 2...

Request 1:

DEFINE FILE SRPROD
WKEND_MDYY/MDYY=DATECVT(SRPROD.DIMDATE.WEEKENDING, 'YYMD', 'MDYY');
INT_CASHIERNO/I10 = EDIT(SRPROD.SRPROD.SRPDCASHIERNO);
INT_STORENO/I5 = IF INT_CASHIERNO GT 9999 THEN INT_CASHIERNO;
STORENO/A5 = EDIT(INT_STORENO);
END
-RUN
TABLE FILE SRPROD
SUM
     SRPROD.DIMSTORE.DSNAME/A50
	 SRPROD.SRPROD.SRPDAVGCUSTPHR
BY HIGHEST SRPROD.SRPROD.SRPDAVGCUSTPHR NOPRINT
BY SRPROD.SRPROD.SRPDCASHIERNAME/A35
BY INT_CASHIERNO
&select.EVAL
WHERE ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING');
WHERE SRPDCASHIERNO NE STORENO;
WHERE SRPDCASHIERNO NE '800';
WHERE SRPDCASHIERNO NE '997';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE1 FORMAT ALPHA
END
-RUN

Request 2:

TABLE FILE SAVE1
??????
??????
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE2 FORMAT ALPHA
END
-RUN

Request 3:

TABLE FILE SAVE2
SUM
     SRPROD.DIMSTORE.DSNAME/A50
	 SRPROD.SRPROD.SRPDAVGCUSTPHR
BY HIGHEST 5 SRPROD.SRPROD.SRPDAVGCUSTPHR NOPRINT
BY SRPROD.SRPROD.SRPDCASHIERNAME/A35
BY INT_CASHIERNO
&select.EVAL
WHERE ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING');
WHERE SRPDCASHIERNO NE STORENO;
WHERE SRPDCASHIERNO NE '800';
WHERE SRPDCASHIERNO NE '997';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE3 FORMAT ALPHA
END
-RUN

How would you write request 2?

No, in the first step sort by cashier by highest average and then hold. The second step will only take the first record for each cashier.
Example:
/*
DEFINE FILE SAVE
SELECT_SW/A1=IF CASHIER NE LAST CASHIER THEN 'Y' ELSE 'N';
END
TABLE FILE SAVE
PRINT * WHERE SELECT_SW EQ 'Y';
ON TABLE HOLD AS SAVE2
END
/*

Step 3 will run against the SAVE2 as name

RSquared,

Thanks for your time and help. Sorry, this is somewhat confusing/new to me. I am trying to get the hang of it all! I promise! haha Just when you think you know things, right?

So, request 1 (sorted by cashier and by highest average as you stated (need the other fields for other stuff)):

DEFINE FILE SRPROD
INT_CASHIERNO/I10 = EDIT(SRPROD.SRPROD.SRPDCASHIERNO);
INT_STORENO/I5 = IF INT_CASHIERNO GT 9999 THEN INT_CASHIERNO;
STORENO/A5 = EDIT(INT_STORENO);
END
-RUN
TABLE FILE SRPROD
SUM
SRPROD.DIMSTORE.DSNAME/A50
BY HIGHEST SRPROD.SRPROD.SRPDAVGCUSTPHR
BY SRPROD.SRPROD.SRPDCASHIERNAME/A35
BY INT_CASHIERNO
&select.EVAL
WHERE ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING');
WHERE SRPDCASHIERNO NE STORENO;
WHERE SRPDCASHIERNO NE '800';
WHERE SRPDCASHIERNO NE '997';
ON TABLE HOLD AS SAVE1
END
-RUN

Request 2:

DEFINE FILE SAVE1
SELECT_SW/A1=IF SRPROD.SRPROD.SRPDCASHIERNAME NE LAST SRPROD.SRPROD.SRPDCASHIERNAME THEN 'Y' ELSE 'N';
END
TABLE FILE SAVE
SUM * (or should I use PRINT like you had because it was all aggregated in the initial request? Does it keep? Yes, you can stick a "3 crayons short of a full box" sticker on my back.)
WHERE SELECT_SW EQ 'Y';
ON TABLE HOLD AS SAVE2
END
-RUN

Request 3:

TABLE FILE SAVE2
PRINT/SUM *
BY HIGHEST 5 SRPROD.SRPROD.SRPDAVGCUSTPHR
ON TABLE SAVE FORMAT ALPHA
END
-RUN

Does that look about right? I think I may have done it right.

Thanks again for your help RSquared. Let me know if I got the above right you think. Thanks!

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

RSquared and everyone else,

After tinkering around with RSquared's idea of multiple requests and example code, the following refined code works for getting either the top or bottom 5 cashiers and their highest/lowest avg of the week designated. So, now, even if a cashier works more than once during the time range specified, the final values will represent either their highest or lowest avg value for that time range. No more multiple entries for a given cashier during a specified time range.

Working final code:

DEFINE FILE SRPROD
WKEND_MDYY/MDYY=DATECVT(SRPROD.DIMDATE.WEEKENDING, 'YYMD', 'MDYY');
INT_CASHIERNO/I10 = EDIT(SRPROD.SRPROD.SRPDCASHIERNO);
INT_STORENO/I5 = IF INT_CASHIERNO GT 9999 THEN INT_CASHIERNO;
STORENO/A5 = EDIT(INT_STORENO);
END
-RUN
TABLE FILE SRPROD
SUM
     SRPROD.DIMSTORE.DSNAME/A50
	 SRPROD.SRPROD.SRPDAVGCUSTPHR
BY HIGHEST SRPROD.SRPROD.SRPDAVGCUSTPHR NOPRINT
BY SRPROD.SRPROD.SRPDCASHIERNAME/A35
BY INT_CASHIERNO
&select.EVAL
WHERE ( SRPROD.DIMDATE.WEEKENDING EQ '&WEEKENDING');
WHERE SRPDCASHIERNO NE STORENO;
WHERE SRPDCASHIERNO NE '800';
WHERE SRPDCASHIERNO NE '997';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SAVE1
END
-RUN

DEFINE FILE SAVE1
SELECT_TOPVALONLY/A1=IF SRPDCASHIERNAME NE LAST SRPDCASHIERNAME THEN 'Y' ELSE 'N';
END
TABLE FILE SAVE1
PRINT *
WHERE SELECT_TOPVALONLY EQ 'Y';
ON TABLE HOLD AS SAVE2
END
-RUN

TABLE FILE SAVE2
PRINT *
BY HIGHEST 5 SRPDAVGCUSTPHR NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SAVE3 FORMAT ALPHA
END
-RUN

Hope this helps all who may come down this path. Thanks again RSquared for your help.