topic Re: Replace Synthetic Key with LinkTable in QlikView

Replace Synthetic Key with LinkTable

Thu, 07 Jan 2016 19:34:30 GMT

Dear QVExperts,

How to replace the Synthetic Table with LinkTable.

Can you please suggest me on this.

Thanks

Sasi

Re: Replace Synthetic Key with LinkTable

marcus_sommer — Thu, 07 Jan 2016 19:40:19 GMT

I wouldn't use a link-table approach and rather concatenate or maybe join these table:

Match:

Load * From Sales;

concatenate

Load * From Return;

concatenate

Load * From Incentive;

Here could find many examples how to develop datamodels: Get started with developing qlik datamodels

- Marcus

Re: Replace Synthetic Key with LinkTable

Anonymous — Thu, 07 Jan 2016 19:54:22 GMT

Marcus is absolutely correct in suggesting you should combine your data into a single table.

But as aside you could convert your [Year} and [Month] fields into a single MonthName field like this :

MonthName(Date#(Year&Month,'YYYYMMM')) as MonthName

Re: Replace Synthetic Key with LinkTable

Fri, 08 Jan 2016 03:16:42 GMT

Thanks Marcus & Bill.

In DataModel, The Synthetic key is removed. But in the Document, two list boxes are expected, Year & Month.

When i split using Left or Right Function, Any one of the List Box is functioning. Either Year or Month.

Can you please suggest me on this.

Thanks

Sasi

Re: Replace Synthetic Key with LinkTable

jagan — Fri, 08 Jan 2016 03:46:31 GMT

Hi,

There is no need of Link Tables every time, you can manage this type of data by concatenating all the tables into one. Check script below

Data:

LOAD Year,

Month,

Sales

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is A);

Concatenate(Data)

LOAD Year,

Month,

Return

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is B);

Concatenate(Data)

LOAD Year,

Month,

Incentive

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is C);

Please find attached file for solution.

Regards,

Jagan.

Re: Replace Synthetic Key with LinkTable

nagarjuna_kotha — Fri, 08 Jan 2016 04:05:28 GMT

Hi sasi,

To avoid systhatic keys ,

Use concatenate and Join Between those table, your problem will resolve.

-Nagarjun

Re: Replace Synthetic Key with LinkTable

engishfaque — Fri, 08 Jan 2016 04:16:28 GMT

Dear Sasi,

Kindly find attached App, I hope you are looking for that one.

Kind regards,

Ishfaque Ahmed

Re: Replace Synthetic Key with LinkTable

rajeshforqlikvi — Fri, 08 Jan 2016 04:16:35 GMT

Yes Marcus solution is correct we need to approach concatenation option.

Re: Replace Synthetic Key with LinkTable

rajeshforqlikvi — Fri, 08 Jan 2016 04:22:35 GMT

Use like this:

Sales:

LOAD Year,

Month,

Sales,

'Origin' as Sales

FROM

(ooxml, embedded labels, table is A);

Return:

Concatenate

LOAD Year,

Month,

Return,

'Origin' as Return

FROM

(ooxml, embedded labels, table is B);

Incentive:

Concatenate

LOAD Year,

Month,

Incentive,

'Origin' as Incentive

FROM

(ooxml, embedded labels, table is C);

by doing this way you can set set analysis very easy way.....

Re: Replace Synthetic Key with LinkTable

PradeepReddy — Fri, 08 Jan 2016 05:29:30 GMT

Concatenation of tables is good option here..

If u don't want, the Sales/Return/Incentive as 3 separate columns.., create a separate Flag field and one Value field

please find the attached file..

Re: Replace Synthetic Key with LinkTable

Fri, 08 Jan 2016 07:58:43 GMT

Thanks Everyone for the valued Suggestions.

Resolved the issue.

Re: Replace Synthetic Key with LinkTable

Mon, 11 Jan 2016 14:48:48 GMT

Hey Sasi,

I work for Pernambuco (State of Brazil) Government, and here we deal with LAAAARGE DATAS. So, we have to use LinkTable like I demonstrate in this attachment.

Your data structure should look like this:

The linktable is constructed like follows:

//HERE THESE LINK TABLES SHOULD NATURALLY BE CONCATENATED

LINKTABLE:

LOAD

YEAR&'-'&MONTH as %YEAR_MONTH_INCENTIVE_KEY,

null() as %YEAR_MONTH_SALES_KEY,

null() as %YEAR_MONTH_RETURN_KEY,

YEAR,

MONTH

FROM

[LINTABLE.xlsx]

(ooxml, embedded labels, table is INCENTIVE);

LINKTABLE:

LOAD

null() as %YEAR_MONTH_INCENTIVE_KEY,

YEAR&'-'&MONTH as %YEAR_MONTH_SALES_KEY,

null() as %YEAR_MONTH_RETURN_KEY,

YEAR,

MONTH

FROM

[LINTABLE.xlsx]

(ooxml, embedded labels, table is SALES);

LINKTABLE:

LOAD

null() as %YEAR_MONTH_INCENTIVE_KEY,

null() as %YEAR_MONTH_SALES_KEY,

YEAR&'-'&MONTH as %YEAR_MONTH_RETURN_KEY,

YEAR,

MONTH

FROM

[LINTABLE.xlsx]

(ooxml, embedded labels, table is RETURN);

The null() statements are for standardize the LinkTable for a natural concatenation.

Re: Replace Synthetic Key with LinkTable

marcus_sommer — Mon, 11 Jan 2016 15:23:36 GMT

It's a possible way but I doubted it that it's best way by LAAAARGE DATAS by which is mostly better to have a single fact-table.

- Marcus

Re: Replace Synthetic Key with LinkTable

Mon, 11 Jan 2016 16:28:46 GMT

But when you have single fact table the data structure doesn't show as much as we need.

I agree that have a single fact is the easiest way, simply concatenate everything... but sometimes we need to "see" what's going wrong, and link table helps a lot.

One fact table for 30 facts from diferent databases will not help.

Giba