topic Re: how to remove syn table in QlikView

how to remove syn table

Anonymous — Wed, 05 Aug 2015 13:10:58 GMT

How can I remove syn table from the following schema

Any help would be the most appreciated .

Re: how to remove syn table

Gysbert_Wassenaar — Wed, 05 Aug 2015 13:13:36 GMT

If you don't experience performance problems then you don't need to remove it. See this blog post: Synthetic Keys

Re: how to remove syn table

Anonymous — Wed, 05 Aug 2015 13:14:48 GMT

define a field with combination from both fields in each table

'Code Structure' & 'Code Rayon' as %CodeStructureRayon

rename both fields at least in one table

build a table with both fields and the new created field %CodeStructureRayon

Re: how to remove syn table

Colin-Albert — Wed, 05 Aug 2015 13:14:53 GMT

Have a look at the links in this post

Data Model

Re: how to remove syn table

vijetas42 — Wed, 05 Aug 2015 13:18:57 GMT

Hi,

please check whether you need to join these two tables on Code Structure and Code Rayon. If you want to join on any one of the column then just rename the second column with different name.so that ,Your synthetic keys will resolved and from your data model syn table will get removed.

Thanks

Re: how to remove syn table

Anonymous — Wed, 05 Aug 2015 13:19:45 GMT

I need both Code Structure and Code Rayon

Re: how to remove syn table

sasiparupudi1 — Wed, 05 Aug 2015 13:23:16 GMT

use qualify statement

qualify Code Structure,Code Rayon;

Re: how to remove syn table

sasiparupudi1 — Wed, 05 Aug 2015 13:52:25 GMT

sorry, forgot to mention , you need the following key field in your tables

'Code Structure' & 'Code Rayon' as %CodeStructureRayon

Re: how to remove syn table

Karim_Khan — Wed, 05 Aug 2015 14:10:39 GMT

You can remove the same by renaming the fields or applying the Link table concept.concept. Pls share ur script for better understanding.

Re: how to remove syn table

Karim_Khan — Wed, 05 Aug 2015 14:12:46 GMT

You can implement the same throigh below code

DIRECTORY;

Budget:

LOAD// [Customer Number],

// Year,

// Month,

[Budget Amount],

[Customer Number] &'|'& Year &'|'& Month as LinkKey //Key Field Genrated

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Budget);

Sales:

LOAD //[Customer Number], //Common Field has Dropped

// Year,

// Month,

[Sales Amount],

[Customer Number] &'|'& Year &'|'& Month as LinkKey //Key Field Genrated

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Sales);

Customer:

LOAD [Customer Number],

[Customer Name]

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Customer);

//Step – 2 Create the Link Table by loading the distinct values of the fact tables

Link:

LOAD Distinct

[Customer Number] &'|'& Year &'|'& Month as LinkKey ,

[Customer Number] &'|'& Year &'|'& Month as Duplicate_LinkKey, //For Checking Duplicate combination while Concatenating

[Customer Number],

Year,

Month

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Budget);

Concatenate (Link)

Load Distinct

[Customer Number] &'|'& Year &'|'& Month as LinkKey, //Concatenate Distinct Combination of Key

[Customer Number],

Year,

Month

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Anil.V\Link & Synthetic Key\LinkSamplData.xlsx]

(ooxml, embedded labels, table is Sales)

WHERE NOT EXISTS(Duplicate_LinkKey, [Customer Number] &'|'& Year &'|'& Month ); //Filter Duplication

DROP Field Duplicate_LinkKey FROM Link;

//Step-3 Load other dimension tables.

Re: how to remove syn table

jonathandienst — Wed, 05 Aug 2015 14:18:33 GMT

Or better yet, do nothing. It may not be a problem at all.