Skip to Content
0

Combine the result set of multiple records?

Apr 12, 2017 at 07:13 PM

55

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Apr 13, 2017 at 04:35 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you for your reply! I will get to work and see how it goes!

0