How to use MAX() function with date field

[Former Member]

Hi Frzz,

I have created a Graphical calculation view in which i have multiple records for each employee with different dates. But my requirement is to take the records which have maximum date.

I have converted the date into Integer and applied the MAX() function. But still am getting multiple records.

Is there is any other way we can achieve this requirement in Graphical Calculation view??  Your suggestion will really help me.

Thank  you.

Krishna.

[Former Member · Accepted solution]

Actually i have aggregated the date field with MAX after converting it into integer.

[Former Member · Accepted solution]

Date values are like

20140101

20140102

20140103

20140104

[former_member182302 · Accepted solution]

Have an aggregation node and keep only date as the column and use max for it. And then try to join this aggregation node with your result table.

Then it will give the records with max date only.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

Thanks for the update.

If i use aggregation node and use max for the Date filed, In this case which fieds act as the Group By claue?? All the remaining fileds are under Group By Clause???

Best Regards,

Krishna.

[former_member182302 · Accepted solution]

Hi Krishna,

Only add DATE filed add it as "Aggregated column" and use MAX for it.

It will frame a query something similar to this: SELECT MAX(DATE) FROM TABLE

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Thanks a lot krishna.

Its working.

[Former Member · Accepted solution]

can you please elaborate?

[lbreddemann]

You seem to require what would be  a sub-select in SQL.

No need to convert the data into an integer - MAX() is defined on date datatypes as well.

What you need to use is an aggregation node and to group by the employee identifier.

The result of this can then be fed into other operators e.g. a join to add detail information.

- Lars

[Former Member]

Thanks for the reply, but sorry I am not clear and not able to achive it yet.

How to achieve the GROUP BY and then what should be the next step please?

[lbreddemann]

Hmm... what have you tried out so far?

Look, I took the effort and created a little example, just for you

Assume we have a table that contains the logon dates of users.

Every line contains the logon date and some info on the user.

Very much like a not-normalized log file could look like.

Now, the output we want is: one line per user with the most current logon time.

Not too difficult:

1. Have a aggregation node that gives you the distinct user details - one line for every user:

2. Have another aggregation node that gives you the last (MAX) logon date per user:

Finally you do a 1:1 join on the USER_ID and map the result to the output.

Easy as pie

- Lars

[draschke]

Hi Lars,

thanks for your little example, its really helpful for beginners.

I followed your steps and could activate my calculation view.

But now, if I check the preview of the semantics I get this error:

If I check only the preview of the Join condition than it works.

Did I forget something in the definitions?

Thanks!!!

[lbreddemann]

Not sure what exactly you "previewed" but when querying calculation views it's (usually) required to have a GROUP BY clause.

The data preview will typically create this group by clause correctly, so I'm not quite sure how you produced this error.

You should however be able to review the generated SQL (via the button) and manually add the required group  by clause in a SQL editor,

[draschke]

I checked the SQL (via the button) and yes its missing. Thanks for this hint!

I found the reason in this post (luna/mars eclipse doesn't generate the groupe by clause)

Where can I manually change the SQL script? I couldn't find it?

[lbreddemann]

The problem here is the SQL that is generated on the fly by the data preview and not part of your model.

So, besides copying this SQL into an SQL editor and adding the missing group by clause, there is not much you can do.

But it shouldn't be a big issue as this only affects the preview of single nodes in the modeller.

[draschke]

I'm not sure, because my xsodata service (I tried to use this calculation view within this service) shows a different result (false) compared to the result in the hana table.

If I check the data in the hana table than I get really the last "time result" as I expect.

But If I call my service than I get a wrong result.

And I thought this error could be the reason and I could fix it.

[former_member307980]

I seem to be looking for a similar thing, but would like to clarify. I am needing to find all of the latest dates in a result set, i.e. all of the records from the latest date.

Any insights on how to do this? Do I use a RANK node? Or add a filter somehow?

Thank you

[rindia]

Hi Krishna,

What are the output fields of your CA view?

Regards

Raj

[Former Member]

Hi Raj,

I have followed what Krishna suggested. Created a aggregated node with date filed and joined with the actual source.

Its working.

Thank you.

Krishna.