Skip to Content

Combine the result set of multiple records?

I am trying to find a way to combine certain columns from different records. Here is the scenario:

When I run the query for "ID" it returns 2 records, however, they have different "Accounting Lines." The top record shows "Date" and "Time" as NULL. The "Accounting Lines" are coming from two separate calc views within my model, so if a record shows "Date" and "Time" as NULL, I want it to automatically reference the other "accounting Line" and bring those records up (from the other Calculation View).

I have made calculated columns with IF statements referencing the columns from both Calculation Views. I know the columns in my IF statement show that they reference one Calc View or the other, but I cannot get those columns I need to match up with the corresponding record from the other calc view.

I thank you in advance for your responses!

capture.png (6.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Apr 13, 2017 at 04:35 AM

    As you do not described how your calc. view looks like, I assume it consumes the data from two calc. views and combines them using a Union All.

    The data coming from both calc. views is separated in different lines, so within a calculated column no access is possible to data records before or after the data record processed by the calculated column.

    If you wanna solve that with pure graphical calc. view options, before you union the result you have to do a left outer join from calc. view a to calc. view b to get the required data together into one line. With that you are able to to your IF expression in the calculated column.

    Another option would be to replace your "data determination" logic by a table function (which can be consumed in a graphical calc. view). Within a table function you could determine the data from your calc. views, union the data and use the LEAD/LAG window functions to access the required data.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded