Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
For a booking there are many invoice line items.
The booking details are in table1 and invoice details are in Table2.
Both the tables can be linked with keyfield bookingID.
-Since there are many common fields between both tables, i see lot of synthetic keys created.
-I wanted to put both TableA and TableB in one table.
What is the best i can do to achieve this?
Should i go with concatenate, left join or anything else?
I have one more query, Is booking table and invoice table both are fact tables. I think no. because both tables have same transaction details.
Regards,
Suraj
I think this will depend on how many times you use each of the underlying tables independently. If the tables are very large and sparse, there can be space considerations.
Generally it is a personal issue, and I would always tend towards having 2 separate tables as the fields in each can then be readily identified for both developers and end users.
At the end of the day there is not much in it.
can you share sample of your data model by using Ctrl+T?
You can put both into the same table by concatenation but if you are going to do this you need to ensure that you mark each source. So the following may work for you:
Load
Book1,
Book2,
CommonRef1,
CommonRef2,
UniqueRef1,
'Booking' as Source,
1 as Booking_Count
from Booking.CSV;
Concatenate
Load
Invoice1,
Invoice2,
Invoice3,
CommonRef1,
CommonRef2,
UniqueRef3,
UniqueRef4,
'Invoice' as Source,
1 as Invoice_Count
from Invoice.CSV
They are both Fact Tables and the above may work for you but without the schema it is difficult to tell.
Hi Rupert,
Thanks a lot for the script.
I am here attaching the same data structure. Please help me with the script.
In your script I didn't understand the functionality of fields-
1 as Invoice_Count
1 as Booking_count
Please explain
Aha ....
Much easier than expected
Invoice:
Load
InvoiceID,
BookingID,
ItemID,
ItemName,
CurrencyID,
InvoiceType,
FromDate as Inv_FromDate,
ToDate as Inv_ToDate,
Price as Inv_Price,
Discount as Inv_Discount
etc etc,
1 as Invoice_Lines
from Invoice.CSV;
Booking:
Load
BookingID,
StartDate as Book_StartDate,
EndDate as Book_EndDate,
Qty as Book_Qty,
StudentID,
BookingOrigin,
etc etc,
1 as Bookings
from Booking.CSV;
The above will join on the common field BookingID (which appears in both Booking and Invoice). There are no other common fields so all should be well.
If there are other fields that have the same name, these are either common to both tables or unique. If they are unique in both tables, one of the can be renamed using "AS". This is a useful method to fully define fields for the end user or developer.
Hope this helps
Sorry, the 1 as Invoice_Lines and 1 as Bookings in the above code is used so that you can count the number of bookings and/or invoices using SUM rather than COUNT which is more efficient
Hi Suraj,
If you left join the booking table with invoice table you will get the following output which is in below attached QVW file.
Hi Rupert/Ishwar,
Thanks a lot for your input.
-As Rupert mentioned, I need to Keep the link between both tables and rename the common field names. This is resolving the issue.
-As Khutal mentioned, doing left join the booking table and renaming common field names, also resolving the issue.
-The 2nd approach finally brings a single table, which i think looks good.
I am still confused about which is the best approach to go with.
kindly suggest..
Thanks a lot..
I think this will depend on how many times you use each of the underlying tables independently. If the tables are very large and sparse, there can be space considerations.
Generally it is a personal issue, and I would always tend towards having 2 separate tables as the fields in each can then be readily identified for both developers and end users.
At the end of the day there is not much in it.
Thanks a lot for all the support !!