topic Re: modelling in QlikView

modelling

surajap123 — Tue, 29 Apr 2014 09:00:02 GMT

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

Re: modelling

vijetas42 — Tue, 29 Apr 2014 09:07:31 GMT

can you share sample of your data model by using Ctrl+T?

Re: modelling

Roop — Tue, 29 Apr 2014 09:45:25 GMT

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.

Re: modelling

surajap123 — Tue, 29 Apr 2014 10:09:42 GMT

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

Re: modelling

Roop — Tue, 29 Apr 2014 10:34:43 GMT

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

Re: modelling

Roop — Tue, 29 Apr 2014 10:36:09 GMT

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

Re: modelling

Tue, 29 Apr 2014 10:37:59 GMT

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.

Re: modelling

surajap123 — Tue, 29 Apr 2014 10:58:21 GMT

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..

Re: modelling

Roop — Tue, 29 Apr 2014 11:06:21 GMT

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.

Re: modelling

surajap123 — Tue, 29 Apr 2014 11:09:07 GMT

Thanks a lot for all the support !!

Re: modelling

Tue, 29 Apr 2014 11:10:52 GMT

Hi Suraj,

this is depends upon your requirement if you want only linking between two table then go with rupert solutions , but you you want booking table data related to invoice table then left join booking table with invoice table and apply some filter according to your requirement.