Oracle Communities

Oracle Analytics and AI

Child Item

How can i pick up correct value based on the date

I am working on a requirement that requires me to get a count of job applicants to specific job groups based on date they applied, if they applied to same job group within six months, then I pick up the first application, if its on different job groups within the six months then I count both applications. I have done my modeling but for some reason when I run the analysis, it shows that my logic is not picking up or working as expected and I am not sure where its going wrong. As you can see from my attached screenshot, when an applicant has multiple applications on same date for same job group or counting both of them instead of counting just the first application. Question if there is way to code this in the anaylysis, I have tried MIN on the date but its not working. any suggestion will be welcome

Find more posts tagged with

Accepted answers

All comments

Robert Angel

Hi,

I know you did not like my advice before, but we have a phrase in Yorkshire; "You cannot make a silk purse out of a sows ear".

In this context, you will not get good results out of a faulty data model.

You have bigger issues than this one query, which if you never resolve you will always be stuck with the same problem, analysis that should be easy to write but just do not work as they should, because of the underlying issues with your data architecture.

This is what Gianni was trying to tell you, and this is what I would have told you sooner had you disclosed previously that this was OBIA, which a number of us on this board will loudly tell you is very much a 'sows ear'.

ForSly

That was not the case, I liked your suggestion only part is how will I get the DDL 's for all the tables involved? please tell me and I will supply all of that information right now

ForSly

I will supply all of the info, please tell me how you want the logical tables / mappings be captured and uploaded up here? also will it help if I shared the starting point of the view that I am using to create the final table?

Robert Angel

Hi ForSly,

re: DDL - it is not this I need to see, it is the rpd modelling, if you only have 'front end' access to OBIA then I am fairly sure i cannot help you, except to advise the massively sub-optimal workaround of writing a direct database request to get your result.

And seriously, I have implemented OBIA previously and really hate the shoddy job the originators did, if your implementation partner is still on site then you need to work with them to resolve the issues.

Really, genuinely, sorry, but with OBIA it is a long way from a fast fix that I could successfully advise over a forum.

ForSly

Fortunately or unfortunately depending on how you look at it, we did not use OBI rpd that comes with the package but rather created own rpd and used the ODI tool that comes with the OBIA for ETL, so really I am not using the rpd that comes with OBIA does that make it easy then ?

Robert Angel

Sure, for the dimensions / fact(s) in the analysis can you make screenshots of; -

business model relationship, dimension hierarchies with keys visible and content level settings on logical table sources

If the physical layer is more complex than

Fact -> dim1 (join via FK)

Fact -> dim2 (join via FK)

for the physical layer then can you also provide details (physical diagram) of tables involved there.

Until we see 'under the hood' we cannot tell you why....

ForSly

Hi Robert,

Sorry was off for a week or so. Are still available to answer my question if I supplied the above info?

Robert Angel

Yes.

Robert Angel

Though if you can access the space you are doing better than me this morning!