How to Get Unique Material information from a Company Sales Transection Data files

[satrajit_chatterjee]

Requirement

This is a very common requirement to found unique material or unique object form a list of object list. Sometimes it is a company transaction information,  From where you need to find the unique object information.

To Get Free HANA Clout system please have a look in this Video

Free SAP HANA Server Access in Cloud - YouTube

Data Files :

Please consider the following data file as a transaction information of a company.

	MAT_ID	MAT_DESC	Sale_Date	unit_price	Qty
1	MAT0001	Paper Tape 62mm x 30.48m	08/22/2015	200	3
2	MAT0002	DCPJ125/J315w- Printer	04/22/2015	3200	4
3	MAT0002	DCPJ125/J315w- Printer	05/22/2015	3200	5
4	MAT0004	Laptop - PK-100	01/22/2015	36000	6
5	MAT0005	TN2320 | 2600 str - Mouse	07/14/2015	320	2
6	MAT0006	STARCOM Dawid - Keyboard	08/14/2015	560	7
7	MAT0002	DCPJ125/J315w- Printer	08/14/2016	3200	8
8	MAT0003	12mm BLACK ON WHITE	09/14/2015	30	23
9	MAT0003	12mm BLACK ON WHITE	10/21/2015	30	12
10	MAT0005	TN2320 | 2600 str - Mouse	11/22/2015	320	1
11	MAT0002	DCPJ125/J315w- Printer	12/22/2015	3200	5
12	MAT0006	STARCOM Dawid - Keyboard	01/22/2016	560	7
13	MAT0002	DCPJ125/J315w- Printer	02/22/2016	3200	8
14	MAT0004	Laptop - PK-100	03/22/2016	36000	32
15	MAT0003	12mm BLACK ON WHITE	04/22/2016	30	2
16	MAT0002	DCPJ125/J315w- Printer	05/22/2016	3200	4
17	MAT0003	12mm BLACK ON WHITE	06/22/2016	30	11
18	MAT0005	TN2320 | 2600 str - Mouse	08/21/2016	320	3
19	MAT0006	STARCOM Dawid - Keyboard	07/21/2016	560	44
20	MAT0003	12mm BLACK ON WHITE	08/22/2016	30	52
21	MAT0004	Laptop - PK-100	08/22/2015	36000	1
22	MAT0006	STARCOM Dawid - Keyboard	04/12/2015	560	6
23	MAT0005	TN2320 | 2600 str - Mouse	05/22/2015	320	7
24	MAT0005	TN2320 | 2600 str - Mouse	06/22/2015	320	9
25	MAT0006	STARCOM Dawid - Keyboard	07/22/2015	560	9
26	MAT0002	DCPJ125/J315w- Printer	08/22/2015	3200	5
27	MAT0004	Laptop - PK-100	08/22/2016	36000	3
28	MAT0002	DCPJ125/J315w- Printer	09/30/2015	3200	20
29	MAT0003	12mm BLACK ON WHITE	10/31/2015	30	21
30	MAT0003	12mm BLACK ON WHITE	11/22/2015	30	7
31	MAT0001	Paper Tape 62mm x 30.48m	08/22/2015	200	3
32	MAT0002	DCPJ125/J315w- Printer	04/22/2015	3200	4
33	MAT0002	DCPJ125/J315w- Printer	05/22/2015	3200	5
34	MAT0004	Laptop - PK-100	06/22/2015	36000	6
35	MAT0005	TN2320 | 2600 str - Mouse	07/22/2015	320	2
36	MAT0006	STARCOM Dawid - Keyboard	08/22/2015	560	7
37	MAT0002	DCPJ125/J315w- Printer	08/22/2016	3200	8
38	MAT0003	12mm BLACK ON WHITE	09/30/2015	30	23
39	MAT0003	12mm BLACK ON WHITE	10/31/2015	30	12
40	MAT0005	TN2320 | 2600 str - Mouse	11/22/2015	320	1
41	MAT0002	DCPJ125/J315w- Printer	12/22/2015	3200	5
42	MAT0006	STARCOM Dawid - Keyboard	01/22/2016	560	7
43	MAT0002	DCPJ125/J315w- Printer	02/22/2016	3200	8
44	MAT0004	Laptop - PK-100	03/22/2016	36000	32
45	MAT0003	12mm BLACK ON WHITE	04/22/2016	30	2
46	MAT0002	DCPJ125/J315w- Printer	05/22/2016	3200	4
47	MAT0003	12mm BLACK ON WHITE	06/22/2016	30	11
48	MAT0005	TN2320 | 2600 str - Mouse	08/21/2016	320	3
49	MAT0002	DCPJ125/J315w- Printer	05/22/2015	3200	5
50	MAT0004	Laptop - PK-100	06/22/2015	36000	6

Target Report

Now we need a report like as follows.

Unique Material

MAT_ID	MAT_DESC
MAT0001	Paper Tape 62mm x 30.48m
MAT0002	DCPJ125/J315w- Printer
MAT0004	Laptop - PK-100
MAT0005	TN2320 | 2600 str - Mouse
MAT0006	STARCOM Dawid - Keyboard
MAT0003	12mm BLACK ON WHITE

Solution

2) Take a Projection and consume the Table in Projection

3) Take Material & Description (Take all those column for which you need unique rows)

4) Create a calculation Column with Fixed value 1

5) Add an Aggnation note on top of that. Then add new column as an aggregated column.

6) Now Add this aggregation note to Projection.

7) Then Activate and execute. It will return you the unique record output.

Please let me know if you have any issue on this.

Regards

Satrajit