topic Re: Remove Synthitic Tables in QlikView

Remove Synthitic Tables

Thu, 16 Jul 2015 08:25:26 GMT

Hi Team,

Please help me out in removing sync tables from my application.

Attached is the qvw file for your reference

I have used Adventure-works sample database

Regards

Priyanka

Re: Remove Synthitic Tables

alexandros17 — Thu, 16 Jul 2015 08:27:11 GMT

Take a look at this example

Re: Remove Synthitic Tables

MK_QSL — Thu, 16 Jul 2015 08:29:13 GMT

Modified Date is different for each table.. The logic behind creating a composite Key as Key looks wrong.

Also,

Hash128(DepartmentID,ModifiedDate) as Key is different to

Hash128(ModifiedDate,BusinessEntityID,DepartmentID) as Key

You can't join like this...

Re: Remove Synthitic Tables

Thu, 16 Jul 2015 08:32:40 GMT

Hi Manish,

Thank you for your instant response.

So can you tell me how will i resolve this issue and what are the points that need to be considered when creating a composite key.

Regards

Priyanka

Re: Remove Synthitic Tables

MK_QSL — Thu, 16 Jul 2015 08:37:42 GMT

1) In your case, what I think is Modified Date is only for Incremental Load. While working on data model, we need to ask ourselves whether we really need all fields or not.

2) If you don't need Modified Date in your final output, you can instantly removed from all your table.

3) As all tables modified Date is different, you can rename them as Depratment_ModifiedDate etc so that there would be not question of having same field name and the auto association between table will not happen.

4) while joining tables (irrespective of number of columns you are joining), the fields must be same. In your case you have created Key with different combination which is wrong.

Re: Remove Synthitic Tables

Thu, 16 Jul 2015 08:57:51 GMT

Hi Manish,

Thank you for the detailed explanation.

I have reworked on the application but still Three tables are forming cyclic reference.

Attached is the application for your reference.

Please help me out in getting rid of this cyclic reference

Thanks in advance

Regards

Priyanka

Re: Remove Synthitic Tables

krishna20 — Thu, 16 Jul 2015 13:45:06 GMT

Hi Priyanka,

First of all thank you for raising this question.

Hi Manish,

In this scenario why can't we choose creating Master Calendar ,for the Modification date through Linked table.

Please let me know.

Re: Remove Synthitic Tables

MK_QSL — Thu, 16 Jul 2015 14:03:35 GMT

Because what I can see from the data model and data that modification date is actually a field which is used for incremental load as any data on any line will change. I don't think so we need any modification date in UI side.

Re: Remove Synthitic Tables

Thu, 16 Jul 2015 14:59:27 GMT

Hi Team,

I have 3 tables name Employees, Suppliers, Customers having 2-3 columns in common.Due to which it is creating synthetic table.

My concern is that City column is present in all the three tables & it contain almost 95 distinct records & 20-25 records are in common between this three tables.

Please advice me how should I remove this synthetic table.

I have attached the QVW file for your reference.

Regards

Priyanka

Re: Remove Synthitic Tables

Thu, 16 Jul 2015 15:01:57 GMT

Hi Manish,

Yes you are right, if we not need any column in UI we can ignore it and the same thing is done by us.

But, If in case I need that, how will I resolve it. Please Advice.

Regards

Priyanka

Re: Remove Synthitic Tables

Thu, 16 Jul 2015 15:04:26 GMT

Thank you Krishna for making me thing from that point of view.

If you know the process or have any reference to resolve this issue, Please do share it with me.

Thanks in advance

Regards

Priyanka

Re: Remove Synthitic Tables

MK_QSL — Thu, 16 Jul 2015 15:10:17 GMT

There are many solutions

1) You can rename all modification date to different name so no auto association will happen.

2) If you want to create a common calendar for all modification date, you can use

i) Different Master Calendar for each date Creating A Master Calendar

ii) Canonical Calendar Canonical Date

3) You can create a Link Table all common fields to remove synthetic keyshow to create link table in Qlikview?

4) You can use Autonumber, AutoNumberHash128 or AutoNumberHash256 or Hash128, Hash160, Hash256 to create Key which you was doing.

Hope this would help. !!

Re: Remove Synthitic Tables

krishna20 — Fri, 17 Jul 2015 04:59:09 GMT

Hi Priyanka,

As of my knowledge there is no common key between three tables i.e; Employees, Suppliers, Customers.

So you can easily resolve the synthetic keys by renaming the City and country fields.

For Eg;

Employee:

Employee Id,

City as [Employee's City]

Country as [Employee's Country];

Suppliers:

Supplier Id,

City as [Supplier's City]

Country as [Supplier's Country];

Customer:

Customer Id,

City as [Customer's City]

Country as [Customer's Country];

Hope it helps.

Re: Remove Synthitic Tables

Fri, 17 Jul 2015 05:27:02 GMT

Hi Krishna,

Thank you for your response.

In the above scenario, out of 95 cities 20-25 cities are matching in this three tables.

My concern is that should I need to proceed with the same logic or we have to think something different.

Regards

Priyanka

Re: Remove Synthitic Tables

krishna20 — Fri, 17 Jul 2015 05:40:37 GMT

Hi Priyanka,

There should be one Key which links one to the other tables.But in your app no key like that.

Customer and Supplier tables also having data differently except city & country for some rows.

you can proceed by renaming the fields.

Re: Remove Synthitic Tables

Fri, 17 Jul 2015 05:46:43 GMT

Hi Krishna,

Thanks for the response.

I will use this concept & if it works fine, will mark your post helpful.

Once again a big thanks

Regards

Priyanka

Re: Remove Synthitic Tables

kavita25 — Fri, 17 Jul 2015 05:46:50 GMT

Hi,

In the attached QVW, there's no key to link all the three tables.

So Instead of using qualify for specific fields, use Qualify * which will rename all the fields in the tables.

Hope it helps you!!

Regards,

Kavita

Re: Remove Synthitic Tables

settu_periasamy — Fri, 17 Jul 2015 06:31:47 GMT

Hi,

May be like this..

Just i tried, may be this gives an idea.

PFA for reference

Edit: Snapshot changed