Joining based on Less than condition between Views

[darshansomaiah1]

Hi Everyone,

The following are the output details of one of the analytic view ZAN_VIEW1 details with respect to MTTF (Mean Time To Failure) where,

EQUNR - Equipment number, START_MALF_MNTH - Start of Malfunction Month, TIME_STAMP - Time stamp of Malfunction, TIMESTAMP_START - Equipment Start Date, AUSZT - Breakdown duration

In another calculation view, ZCV_VIEW2, I have collected the maximum of Time stamp of Start of Malfunction for the respective month. TIME_STAMP_1 (Maximum in an aggregation)

For MTTF for the respective months, that is from a calculation view ZCV_VIEW2, START_MALF_MNTH will be selected/ given for filter from the reporting and the maximum time stamp of that month is taken and I have passed to analytic view ZAN_VIEW1 and the condition are as follows, to get the breakdown duration AUSZT as follows,

For Example with 201507, the TIME_STAMP_1 is 2015-07-08 04:00:35.0

Then,

(Reduced a drill down to get the aggregated value with respect to the month in an analytic view)

Here, I could not get how to join in another calculation view on joining the time stamp column with a condition of less than the value with respect to analytic view and calculation view.

Please suggest on its feasibility.

MTTF = (Equipment Start up date/ time - Start of Malfunction Date/ time) - (AUSZT of past values except the start of malfunction date considered)

Best Regards,

Nachappa S

[darshansomaiah1 · Accepted solution]

Hi

Could you please suggest on this requirement feasibility. Thanks in advance.

Regards,

[lbreddemann · Accepted solution]

I don't understand what you want to do.

Can you express it either in SQL or in plain english what information you want to retrieve?

[darshansomaiah1 · Accepted solution]

Hi

I was trying to achieve the values of Breakdown duration for all the month's maximum date of Malfunction start date and with respect to Equipment Start Date.

Tried with calculation view scripts and was able to achieve the following using the calculation view scripts as attached.

And added few joins with analytic view to get the following table details,

Where, I need to subtract the MTTF_CALC3 on the previous row values with respect to the Equipment number and divide the result by NO_BRKDOWN.

The following picture depicts the requirement.

Could you please suggest on how we can subtract the MTTF_CALC3 from the previous row with respect to the equipment/ month (In the above image), since data type of Month is NVARCHAR 6.

Thanks,

[lbreddemann · Accepted solution]

Actually I am more confused on your requirement and the solution approach you took than before.

On an implementation level you seems to wildly mix analytical views with scripted calc views, both CE-functions ans SQL commands.

I have the strong feeling that the usage of the different tools here is rather arbitrary and maybe founded on some believes like *CE functions are faster" and the like.

For me, in order to make a proper recommendation to solve the problem, the source data and the desired output are required.

To answer the short term question of how to access the previous' row values: try Window functions (LAG/LEAD).

[darshansomaiah1 · Accepted solution]

Thanks

LAG windows function was useful.

I am reviewing my design approach after going through your suggestion.

I will update. Thanks again!!