topic Link Table issue in QlikView

Link Table issue

Fri, 01 Jun 2012 05:48:10 GMT

Hi,

I got the situation to connect 2 fact tables.

I have a fact table for transactions on day level(Invoice Date as DateKey) and also have a budget table on month level. Finally, I have a calendar table which has the fields DateKey and Month to join to both fact tables.

i do have one common field Vendor, to connect both Fact tables.

What should my link table look like to make this possible in Qlikview?

Thanks a lot in advance.

Re: Link Table issue

vincent_ardiet — Fri, 01 Jun 2012 08:59:31 GMT

Hi,

You have different solutions, one is creating a pivot table in charge of connecting all the tables of your model.

For example, your table transaction will not contain any direct reference to the date and the vendor but a key composed of the both information (just concatenate the two field with a separator in between). This key will be present in the pivot table too. Your budget table will have a key composed of month and vendor.

So, your pivot table will have a key to join to transaction (date+vendor), a key to join to budget (month+vendor), a key to join to the vendor table, a key to join to the calendar table (DateKey).

QV will handle properly the cardinality.

Regards,

Vincent

Re: Link Table issue

Fri, 01 Jun 2012 09:29:09 GMT

Hi Vincent, Thanks for your reply.if you don't mind, could you please explain a bit more clearly(don't have much idea on modelling)?say for example, i got 4 tables as below:

TRANS-Table:

Datekey,

Vendorkey,

Revenue.

VENDOR-Table:

Vendorkey,

Vendor-Info.

BUDGET-Table:

Month,

Budget.

CALENDAR-Table:

Datekey,

Month.

So, do i need to populate my pivot table with four columns?

Thanks.

Re: Link Table issue

vincent_ardiet — Fri, 01 Jun 2012 12:24:18 GMT

Here is an example :

tmpTRANS:
Load * Inline [
DateKey, VendorKey, Revenue
40909, v1, 150
40910, v2, 200
40918, v1, 175
40942, v2, 240
40953, v1, 230
40973, v3, 1500
40984, v2, 90
] ;

VENDOR:
Load * Inline [
VendorKey, VendorInfo
v1, vendor1
v2, vendor2
v3, vendor3
] ;

tmpBUDGET:
Load * Inline [
Month, VendorKey, Budget
201201, v1, 200
201201, v2, 200
201202, v1, 300
201202, v2, 350
201202, v3, 400
201203, v1, 310
201203, v2, 320
201203, v3, 2000
] ;

tmpCALENDAR:
Load * Inline [
DateKey, Month, MonthName
40909, 201201, Jan
40910, 201201, Jan
40911, 201201, Jan
40912, 201201, Jan
40913, 201201, Jan
40914, 201201, Jan
40915, 201201, Jan
40916, 201201, Jan
40917, 201201, Jan
40918, 201201, Jan
40919, 201201, Jan
40920, 201201, Jan
40921, 201201, Jan
40922, 201201, Jan
40923, 201201, Jan
40924, 201201, Jan
40925, 201201, Jan
40926, 201201, Jan
40927, 201201, Jan
40928, 201201, Jan
40929, 201201, Jan
40930, 201201, Jan
40931, 201201, Jan
40932, 201201, Jan
40933, 201201, Jan
40934, 201201, Jan
40935, 201201, Jan
40936, 201201, Jan
40937, 201201, Jan
40938, 201201, Jan
40939, 201201, Jan
40940, 201202, Feb
40941, 201202, Feb
40942, 201202, Feb
40943, 201202, Feb
40944, 201202, Feb
40945, 201202, Feb
40946, 201202, Feb
40947, 201202, Feb
40948, 201202, Feb
40949, 201202, Feb
40950, 201202, Feb
40951, 201202, Feb
40952, 201202, Feb
40953, 201202, Feb
40954, 201202, Feb
40955, 201202, Feb
40956, 201202, Feb
40957, 201202, Feb
40958, 201202, Feb
40959, 201202, Feb
40960, 201202, Feb
40961, 201202, Feb
40962, 201202, Feb
40963, 201202, Feb
40964, 201202, Feb
40965, 201202, Feb
40966, 201202, Feb
40967, 201202, Feb
40968, 201202, Feb
40969, 201203, Mar
40970, 201203, Mar
40971, 201203, Mar
40972, 201203, Mar
40973, 201203, Mar
40974, 201203, Mar
40975, 201203, Mar
40976, 201203, Mar
40977, 201203, Mar
40978, 201203, Mar
40979, 201203, Mar
40980, 201203, Mar
40981, 201203, Mar
40982, 201203, Mar
40983, 201203, Mar
40984, 201203, Mar
40985, 201203, Mar
40986, 201203, Mar
40987, 201203, Mar
40988, 201203, Mar
40989, 201203, Mar
40990, 201203, Mar
40991, 201203, Mar
] ;

TRANS:
Load
DateKey&'-'&VendorKey as TRANSkey,
Revenue
Resident tmpTRANS ;

BUDGET:
Load
Month&'-'&VendorKey as BUDGETkey,
Budget
Resident tmpBUDGET ;

CALENDAR:
Load
DateKey,
MonthName
Resident tmpCALENDAR ;

PIVOT:
Load
DateKey&'-'&VendorKey as TRANSkey,
DateKey,
VendorKey,
num#(date(DateKey,'YYYYMM')) as Month,
date(DateKey,'YYYYMM')&'-'&VendorKey as BUDGETkey
Resident tmpTRANS ;
Load
null() as TRANSkey,
num(makedate(left(Month,4),right(Month,2))) as DateKey,
VendorKey,
Month,
Month&'-'&VendorKey as BUDGETkey
Resident tmpBUDGET ;

Drop Table tmpTRANS ;
Drop Table tmpBUDGET ;
Drop Table tmpCALENDAR ;

Regards,

Vincent

Re: Link Table issue

Sat, 02 Jun 2012 02:01:40 GMT

Thank you, Vincent.