topic Re: concatenate or left join in QlikView

concatenate or left join

surajap123 — Fri, 25 Apr 2014 08:45:30 GMT

How do i know if have 2 fact tables.

We have 2 tables which contain both measures and dimension in each table.

eg-invic table contain invoice amount and various dimensions.

eg-booking table contain booking quanity and various dimenssions.

both these tables have common booking id.

I want to know whether i need to concatenate or left join or create link table get right results from these 2 tables.

Re: concatenate or left join

its_anandrjs — Fri, 25 Apr 2014 08:50:27 GMT

Create a link table if Booking Id is not unique in the tables or may be concatenate.

Re: concatenate or left join

MK_QSL — Fri, 25 Apr 2014 08:52:44 GMT

Simple Join or Concatenate will not work ... may be Interval match can help you.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Re: concatenate or left join

Fri, 25 Apr 2014 08:55:09 GMT

You should go for Concatenate.

Because Concatenate just appends the table one below other. So there won't be any repeatation of data and it will also fulfil your requirements.

Hope this will help u..!

Re: concatenate or left join

surajap123 — Fri, 25 Apr 2014 09:04:40 GMT

Hi Anand,

The Booking id is unique. So i go with concatenate?

The business works like this-->

when a booking is made, a start and end date is created in booking table based on the duration of the course. Then when booking is confirmed then invoice is generated and there can be any number of invoiceline items. Each line item falls within the date range of booking start and end date.

In brief the the invoice start and end date ALWAYS falls within booking start and end date.

@Manish- I am not sure if i can use interval match, as both booking and invoiceline has start and end dates. (Each invoice line falls within booking start and end date)

Re: concatenate or left join

surajap123 — Fri, 25 Apr 2014 09:15:23 GMT

Hi Priya,

Thanks for the reply.. If you see both the tables, there are many fields which are not common. so, do you think i still need to go with concatenate?

Re: concatenate or left join

MK_QSL — Fri, 25 Apr 2014 09:18:40 GMT

Why you want to Join or Concatenate?

As there are one field common in both tables, QlikView script will do automatic association between these two tables !

Re: concatenate or left join

Fri, 25 Apr 2014 09:20:39 GMT

yeah...even though the fields are not common u can go for com\ncatenate.

Even u can use the generic keys for which field are not common.

Like if in 1st table u have booking_id field is present and in 2nd table it is not,

then u can write below script in 2nd table:

Load 990 as booking_id

from Table 2;

This keys are used so that in 2nd table for booking_id field it will assign 990 as dummy value for all rows.

Hope this will help u..!

Re: concatenate or left join

its_anandrjs — Fri, 25 Apr 2014 09:23:43 GMT

I suggest you leave it in association state or may be you can left join the tables like

Invoice:

Load *

from location;

left join(Invoice)

Load *

from location;

Re: concatenate or left join

surajap123 — Fri, 25 Apr 2014 09:49:44 GMT

Thanks all you guys for helpful responses and time spent on my issue !!