Categories
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
Answers
-
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'.
0 -
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
0 -
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?
0 -
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.
0 -
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 ?
0 -
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....
0 -
Hi Robert,
Sorry was off for a week or so. Are still available to answer my question if I supplied the above info?
0 -
Yes.
0 -
Though if you can access the space you are doing better than me this morning!
0