synthesis key

[arulsettu]

hi

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?

[MK_QSL]

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.

[arulsettu]

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

[its_anandrjs]

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;

[arulsettu]

so it will create a single table?

[qlikoqlik]

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

[its_anandrjs]

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