Combine two or more tables into one  [SOLVED]

Discussion & Feedback for Jet Enterprise
RYazigi
Posts: 4
Joined: Mon Jan 02, 2017 6:23 am

Combine two or more tables into one

Postby RYazigi » Mon Jan 02, 2017 7:47 am

Hi All,

I am quiet new to Jet Enterprise, but not new to BI, and i was wondering if there is a way (other than creating a view on staging) to combine one or more source table into one Destination something like Union (not Union all).

an example:
table A: has the data (One, Two, Three)
Table B: Has the data (Three, Four, Five)

as a result i would like to have only
Table DW (One, Two, Three, Four, Five)...

three must not be repeated.
i created a view and solved my prloblem but as mentioned above, just checking if there is another way.


Thanks
RY.

ckhan1084
Posts: 1
Joined: Tue Nov 01, 2016 3:01 pm

Re: Combine two or more tables into one

Postby ckhan1084 » Tue Jan 03, 2017 1:54 pm

Hello,

It appears that the simplest resolution for you will likely be creating a conditional lookup from a table in the data warehouse that can get records from both tables A and B.
I have attached some screenshots for reference but the easiest way to denote which fields within a table in your project are currently lookups are fields with a red field icon (as seem in the first screenshot).
To create your own conditional lookup, you can right-click the desired table and select the Add Conditional Lookup Field option.

Within the Lookup Field, you can specify the field as well as the specific join you would like to create.

Please let us know if this helps and if you have any other questions regarding this.
Thank you so much.

Regards,
Chris Khan
Attachments
Conditional Lookup 02.png
Conditional Lookup 02.png (8.11 KiB) Viewed 661 times
Conditional Lookup 01.png

RYazigi
Posts: 4
Joined: Mon Jan 02, 2017 6:23 am

Re: Combine two or more tables into one  [SOLVED]

Postby RYazigi » Tue Jan 10, 2017 6:10 am

Hi,

thank you for your reply, the solution you provided would solve the Problem only if the table consists of one field (as my example was with only one field i guess that answers it :) my bad there).

but what I really wanted was combining tables with several columns, i have found a different solution than the view mentioned in my earlier post, and bellow is the solution I found in case someone else needs to do the same.

i will restate the Problem:
i Need to Combine more than one table in the same target table and to do so we Need to :
1. in staging: grab both the tables you Need I will refer to them source1 and source2
2. in staging: create a third table "consolidated_source" with the same structure (optionally we can add an extra field to identify the source of the the data)
3. in staging:
a. right click "consolidated_source" and choose "Advanced" --> "Add Table Insert" Option
b. fill the needed fields (Name, source table source1)
c. map the fields from source
d. optionally add "source1" to the extra field (added in the optional step)
e. repeat (b....d) for source2, source3....sourceN
4. drag and drop "consolidated_source" to the DWH

other Options are to remove the "add phsical valid table" to the original sources if space is an issue.

i always prefer tables rather than views as i would like to preserve the data for Analysis if something breaks. but the view is a faster way of going around the issue (and the extra field could be also included)

Best Regards
RY.


Return to “How do I?”

Who is online

Users browsing this forum: No registered users and 2 guests