cancel
Showing results for 
Search instead for 
Did you mean: 

Exclude duplicate records at Calculation View

Former Member
0 Kudos

Hi All,

I have a requirement where I need restrict data(records) of HANA View based on below condition.

ORDER

MATERIAL

DATE

QTY

101

MAT1

02032016

100

102

MAT1

05032016

200

103

MAT2

06032016

300

104

MAT3

06032016

400

Out of above data, I need to delete ORDER and  MATERIAL based on Max DATE.

Means, I need ORDER : 102.  Need to sort Material based on Max DATE.

As 05032016 is Max Date for MAT1 , I need ORDER =102 and exclude Order-101 .

When I preview data of my CALCULATED VIE, I need only one record(ORDER -102,103,104) out of 4.

How can I achieve this at HANA Calculation View end.



Thanks.

Accepted Solutions (0)

Answers (6)

Answers (6)

rindia
Active Contributor
0 Kudos

Hi Ram,

We can do this by using Sqlscript CV:

The script is:

/********* Begin Procedure Script ************/

BEGIN

  var_out =      SELECT DISTINCT ORDER_NUMBER,MATERIAL, DATE, QTY

                      FROM "MATERIAL"

                      WHERE  DATE IN (     SELECT MAX(DATE)

                                                         FROM "MATERIAL"

                                                         GROUP BY MATERIAL

                                                    )

                      ORDER BY ORDER_NUMBER

                      ;

END /********* End Procedure Script ************/

Regards

Raj

Former Member
0 Kudos

hi Ram,

Please find the steps to achieve the result.

Input Table:

Output:

Former Member
0 Kudos

Hi Ram,

I have executed the scenario and could achieve the output required as below

Table

Model

Output

Best Regards,

Shireesha

kr_pavankumar
Contributor
0 Kudos

Hi Shireesha,

Is it required to have MAX for QTY?

--

KRPK

Former Member
0 Kudos

Hi,

I am not sure whether i understood your requirement corrctly or not.

But there is a thread where similar issue has been discussed.You can check this:

I have used similar logic which is given in this thread and i was able to fetch the row with latest date.

Regards,

Shiwali

anindya_bose
Active Contributor
0 Kudos

Hi Ram

Use Rank Node for the same.

Put Material in "Partition By"  and Date in "ORder BY" , Choose Descending and Threshold as 1 . 

Regards

Anindya

Former Member
0 Kudos

HI Bose,

I have included  RANK Node. Still I am getting multiple records(for below scenario).

If both DATEs are same. Still I need one record. Any possibility.

ORDER

MATERIAL

DATE

QTY

101

MAT1

02032016

100

102

MAT1

02032016

200

103

MAT2

06032016

300

104

MAT3

06032016

400

anindya_bose
Active Contributor
0 Kudos

Yes, you can take another Rank node and do the same on Quantity then  ( may be ascending now ) .

Projection--->Rank1---->Rank2---->Aggregation

Regards

Anindya

Former Member
0 Kudos

Hi Ram,

In this case what is the criteria you want based on which the record will be retained/deleted ?

Regards,

Amit

Former Member
0 Kudos

I agree with your point . Its valid.

anindya_bose
Active Contributor
0 Kudos

By the way, some of the filtering , ranking you can leave for Reporting tool as well  unless you have huge data volume ,  then reporting user would have more flexibility  

Regards

Anindya

Former Member
0 Kudos

Hi Bose,

Is there any way to consider 2 Fields at ORDER BY.

As of now, system is allowed me to keep only one Fields at " ORDER BY". What if I have more that one fields to sort Data.

Regards,

Vijay

anindya_bose
Active Contributor
0 Kudos

Rank node is node is not only ordering fields by "Order By" fields , it is also selecting Top /Bottom N number of records.  In your case, it is supposed to show you TOP 1 date .

What you might try is...

Create a Calculated Column in Projection Node .  Define it as concatenation of Date and QTY .

Output should be like ..

02032016100  and 02032016200


Use the calculated column in Order By . 


Note:  You have to make QTY as String/Char before concatenation .  Date format should YYYYMMDD .  This would take Latest Date and highest QTY .

Let me know if that works for you.

Regards

Anindya

Former Member
0 Kudos

Hi Ram,

Use aggregation node for this. Just keep MATERIAL and DATE in the output and keep DATE as the aggregated column and use Max as the Engine Aggregation for DATE.

Then do an inner join between this aggregation node and the projection node where you have the actual information with joining attributes as MATERIAL and DATE.

Regards,

Amit

Former Member
0 Kudos

Hi Das,

What is the sort Condition(Ascending or descending) system considers If I go with Aggregation node ?

Thanks.

Former Member
0 Kudos

Hi Ram,

I didn't get your question. If you are taking the aggregation as Max, then it doesn't matter what is the sort condition. In Aggregation node you don't have the option to sort. For that you need to use the Rank node.

Regards,

Amit