on 03-11-2016 4:15 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.