topic Re: synthesis key in QlikView

synthesis key

arulsettu — Sat, 19 Jul 2014 08:39:24 GMT

i am having three tables like

SQL SELECT CustomerID,

Month,

Year,

invoiceamount

FROM Invoices;

SQL SELECT Amount,

CustomerID,

Month,

Year

FROM Budget;

SQL SELECT CustomerID,

Month,

Year

TargetAmount

FROM Target;

it creating synthesis key in data model. i am trying to display invoice and budget in chart. month and year as drilldown. if i rename it i am not getting exact result. i tried to concatenate but its not giving exact result.

any help?

Re: synthesis key

MK_QSL — Sat, 19 Jul 2014 09:13:57 GMT

You can create a common calendar for Invoice, Budget and Target Month and Year and create a Flag in each table to use in set analysis.

For further information search

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

If you still having problems, kindly supply some dummy/sample data to work.

Re: synthesis key

arulsettu — Sat, 19 Jul 2014 09:22:06 GMT

Every table having three fields in common. so its creating synthesis key i want concatenate month and year fields for three tables any idea?

Re: synthesis key

its_anandrjs — Sat, 19 Jul 2014 09:31:59 GMT

You have to concatenate this tables and then you get single table and rename the field Amount as same name in both three tables see the example load script below.

EX:-

SQL SELECT CustomerID,

Month,

Year,

invoiceamount as Amount,

'Invoices' as TableFlag

FROM Invoices;

Concatenate

SQL SELECT Amount,

CustomerID,

Month,

Year,

'Budget' as TableFlag

FROM Budget;

Concatenate

SQL SELECT CustomerID,

Month,

Year,

TargetAmount as Amount,

'Target' as TableFlag

FROM Target;

Re: synthesis key

arulsettu — Sat, 19 Jul 2014 09:36:43 GMT

so it will create a single table?

Re: synthesis key

qlikoqlik — Sat, 19 Jul 2014 09:49:53 GMT

HI Arul

Join the tables instead of concatenating like below using uniquekey between the tables and renaming the common fields in the other two tables so that they wont create synthetic keys.

LOAD CustomerID,
     Month,
     Year,
     CustomerID &'-'&Month&'-'&Year AS UniqueKey,
     invoiceamount
FROM

(ooxml, embedded labels, table is Invoicing);
join LOAD CustomerID AS BCustomerID,
     Month AS BMonth,
     Year AS BYear,
     CustomerID &'-'&Month&'-'&Year AS UniqueKey,
     invoiceamount as Budget
FROM

(ooxml, embedded labels, table is Budget);
JOIN LOAD CustomerID as TCustomerID,
     Month AS TMonth,
     Year AS TYear,
          CustomerID &'-'&Month&'-'&Year AS UniqueKey,
     invoiceamount as TargetAmount
FROM

(ooxml, embedded labels, table is Target);

Regards

Padma

Re: synthesis key

its_anandrjs — Sat, 19 Jul 2014 10:29:33 GMT

Yes you are right it is the best way and there is another ways of doing the same situation. Concatenate the Budget and Target tables and join with the Invoices table See the example load script

Ex:-

Budget:

SQL SELECT

Amount,

Year&'_'&Month&'_'&CustomerID as %Key,

'Budget' as TableFlag

FROM Budget;

Concatenate

Target:

SQL SELECT

Year&'_'&Month&'_'&CustomerID as %Key,

TargetAmount as Amount,

'Target' as TableFlag

FROM Target;

Join

Invoice:

SQL SELECT

Year&'_'&Month&'_'&CustomerID as %Key,

CustomerID,

Month,

Year,

invoiceamount,

'Invoices' as TableFlag

FROM Invoices;

Note:- Here i assume the data in the tables Budget and the Target are unique in Year, Month and CustomerID

then load table like this way