Hi All,

I am stuck while i try to implement logic of a report using the data i have.

  
Year	Ref_no	   Product_Id	Priority_Id	Sale Count
2017	17003014	13001	11	1
	17003278	35001	19	1
	17003332	50000	27	1
	17003679	50000	27	1
	17004196	26001	13	1
	17004971	23007	6	1
	17008132	13001	11	1
				        2
		        23007	6	1
				        2
	17011293	26001	13	0
				1
	201702418	35001	19	1
	201707530	54001	27	1
	201707568	13002	4	0
				1
	201707811	50000	27	1
	210013621	54002	22	0
				1
	210014246	54002	22	0
				1
	220011398	54002	22	0
				1
	330003290	23003	6	1
	610001785	11002	2	0

This is how the data is for an year.
For reference number with multiple product_Id i need to display only product_Id details with lowest Priority-Id.
I have to use this data as a HOLD file for rest of the report logic.

Please give some suggestions.

This message has been edited. Last edited by: FP Mod Chuck,

Using the data you show:
for Ref_no 17008132
you want to show only Product_Id 23007 because its Priority_Id is 6? And all Sale Count?

Sounds like you need:

BY LOWEST 1 Priority_Id

Hi srajeevan,
I took below sample data and created a master file called Prod_Ref

 Ref_no	Product_Id	Priority_Id	SaleCount
17008132	13001	11	1
17008132	13001	11	2
17008132	23007	6	1
17008132	23007	6	2
17003014	13001	11	1
17003278	35001	19	1
17003332	50000	27	1
17003679	50000	27	1
17004196	26001	13	1
17004971	23007	6	1 

Below is the WF code to get the min priority prod

 TABLE FILE PROD_REF
SUM
MIN.PRIORITY_ID WITHIN  PROD_REF.PROD_REF.REF_NO AS 'MIN'
BY  PROD_REF.PROD_REF.REF_NO
BY  PROD_REF.PROD_REF.PRODUCT_ID
BY  PROD_REF.PROD_REF.PRIORITY_ID
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS MIN
END

DEFINE FILE MIN
FLAG/A2 = IF(PRIORITY_ID EQ MIN) THEN 'Y' ELSE 'N';
END 
TABLE FILE MIN
PRINT 
FLAG
BY  REF_NO
BY  PRODUCT_ID
BY  PRIORITY_ID
WHERE  FLAG EQ 'Y'
END 

Report output:-

  REF_NO PRODUCT_ID PRIORITY_ID FLAG 
17003014 13001 11 Y 
17003278 35001 19 Y 
17003332 50000 27 Y 
17003679 50000 27 Y 
17004196 26001 13 Y 
17004971 23007 6 Y 
17008132 23007 6 Y 

quote:

Originally posted by Danny-SRL:
Using the data you show:
for Ref_no 17008132
you want to show only Product_Id 23007 because its Priority_Id is 6? And all Sale Count?

Yes @Danny-SRL.I want to show only Product_Id 23007 with its Sale Count.

quote:

Originally posted by Wep5622:
Sounds like you need:

BY LOWEST 1 Priority_Id

@Wep5622..

 BY LOWEST 1 Priority_Id 

wont work because Priority_Id is sorting based on Product_Id.
Ex: for Ref_no 17008132 two Product_Id are there..so When i give

 BY LOWEST 1 Priority_Id  

,for the Product_Id 13001 lowest is 11 itself and for 23007 lowest priority_id id 6..so both will display.

@Chaudhary.

You solution is working for me.
Thanks. Saved my day.

quote:

You solution is working for me.
Thanks. Saved my day.