The story below concerns the attached workbook in which Query is a Microsoft SQL server datasource and 2018 - 3+ Holding is an Excel datasource.
In sheet 1 I have the following table (note the screen below shows an excerpt from the table. Also note the datasource, which is "Query" in this case). The table consists of ParentRekeningen, Rekeningen and 'Trailer' values:
In sheet 2 I have another table, which is similar to the one above. The difference is that another datasource is used with other values (see 'Holding' values). The 'ParentRekeningen' and 'Rekeningen' are partly the same and some are unique (i.e. ParentRekening FFA? Fixed Financial Assets and its Rekeningen (0191, 0192 etc.). These do not appear in sheet 1, datasource Query):
In sheet 3 I want to consolidate the contents from sheet 1 and sheet 2. However, up to now I only managed to merge the ParentRekeningen and Rekeningen that both datasources have in common (below an excerpt from the table in sheet 3 is shown. An example of a common ParentRekening/Rekening is 0210. Trailers has a value of -18.000, while holding has a value of -18.160):
However, is there also a way to add the ParentRekeningen and/or Rekeningen that are unique to the datasource?
So for instance, Rekening 0191 does not appear in the Query datasource, but does appear in the 2018 - 3+ holding datasource. How can we still add Rekening 0191 (and other ParentRekeningen/Rekeningen of the same sort) to sheet 3 such that Trailers gets value 0 and holding the value of the corresponding Rekening (e.g. when 0191 is added to the view, we get ParentRekening->FFA? Fixed Financial Assets --- Rekening-> 0191 --- Trailers->0 --- holding->2)?
Note that also a parameter is present in the sheets. This parameter should still work and is of importance to select the correct period.
Thanks in advance for the support.
Workbook.twbx 117.9 KB