How to Remove Synthetic Tables in Qliksense

[jonesbrown]

Hi 

All

I am new to Qliksense

How to Remove Synthetic Tables in Qliksense

This is my Script Data

[BM Data.QVD]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Prod - Material",
    "Product – Manufacturer",
    "Calendar - Fiscal Year",
    "Calendar - Fiscal Period",
    "GSV Qty 2016",
    "GSV 2016  $"
FROM "lib://QVDs/[BM Data.QVD]"
(qvd);



[BW Waranty Report]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Prod - Material",
    "Product – Manufacturer",
    "Calendar - Fiscal Year",
    "Calendar - Fiscal Period",
    "Total Qty",
    "Overall Std Cost"
FROM "lib://QVDs/[BW Waranty Report.QVD]"
(qvd);



[BW S&M Report-2013]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Prod - Material",
    "Product – Manufacturer",
    "Calendar - Fiscal Year",
    "Calendar - Fiscal Period",
    "GSV Qty 2013",
    "GSV 2013"
FROM "lib://QVDs/[BW S&M Report-2013.QVD]"
(qvd);


[BW S&M Report-2014]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Prod - Material",
    "Product – Manufacturer",
    "Calendar - Fiscal Year",
    "Calendar - Fiscal Period",
    "GSV Qty 2014",
    "GSV 2014"
FROM "lib://QVDs/[BW S&M Report-2014.QVD]"
(qvd);


[BW S&M Report-2015]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Prod - Material",
    "Product – Manufacturer",
    "Calendar - Fiscal Year",
    "Calendar - Fiscal Period",
    "GSV Qty 2015",
    "GSV 2015"
FROM "lib://QVDs/[BW S&M Report-2015.QVD]"
(qvd);


[BW S&M Report-2016]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Prod - Material",
    "Product – Manufacturer",
    "Calendar - Fiscal Year",
    "Calendar - Fiscal Period",
    "GSV Qty 2016",
    "GSV 2016"
FROM "lib://QVDs/[BW S&M Report-2016.QVD]"
(qvd);


[BW Warranty Report-2013]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Service Class",
    "Faulty Tool",
    "Calendar - Fiscal Quarter",
    "Calendar - Fiscal Period",
    "Tool Qty",
    "Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2013.QVD]"
(qvd);


[BW Warranty Report-2014]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Service Class",
    "Faulty Tool",
    "Calendar - Fiscal Quarter",
    "Calendar - Fiscal Period",
    "Tool Qty",
    "Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2014.QVD]"
(qvd);


[BW Warranty Report-2015]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Service Class",
    "Faulty Tool",
    "Calendar - Fiscal Quarter",
    "Calendar - Fiscal Period",
    "Tool Qty",
    "Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2015.QVD]"
(qvd);


[BW Warranty Report-2016]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Service Class",
    "Faulty Tool",
    "Calendar - Fiscal Quarter",
    "Calendar - Fiscal Period",
    "Tool Qty",
    "Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2016.QVD]"
(qvd);


[China Tool Data]:
LOAD
    "Entity - L5 Commercial Sub Region",
    "Entity - L6 GM Region",
    "Service Class",
    "Faulty Tool",
    "Calendar - Fiscal Quarter",
    "Calendar - Fiscal Period",
    "Tool Qty",
    "Overall Std Cost"
FROM "lib://QVDs/[China Tool Data.QVD]"
(qvd);


[Material_Master Data]:
LOAD
    Catalog_Number,
    "Material Description",
    "Material Type",
    "Material Group",
    "Product Hierarchy",
    SBU,
    "SBU Description",
    "Product Division",
    "Product Div Desc",
    Family,
    "Product Family",
    "Group",
    "Group Description",
    "Sub Group",
    "Sub Group Description",
    "Sub Sub Group",
    "Sub Sub Group Desc.",
    Division,
    "Product Brand",
    "Unit per Pak",
    "Unit per KAR",
    FamilyDesc,
    New_ProductDivision
FROM "lib://QVDs/[Material_Master Data.QVD]"
(qvd);

This is my Data Model Viewer

Plz help.

[jochem_zw]

you can create a composite key for the duplicate fields in the different tables. Start simple for example the first 2 tables:

"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period"

combine these fields into 1 Key field in both tables by concatenating them:

Entity - L5 Commercial Sub Region&'_'&Entity - L6 GM Region&'_'&Prod - Material etc.

Try and you will see how it works, https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/synth...

Suc6

[jonesbrown]

I have Created one Composite Key in Two Tables

How can I remove Another synthetic tables 

This my Script Data

[BM Data.QVD]:
LOAD
"Entity - L5 Commercial Sub Region" &'_'&"Entity - L6 GM Region"
&'_'& "Prod - Material"
&'_'&"Product – Manufacturer"
&'_'& "Calendar - Fiscal Year"& '_'&"Calendar - Fiscal Period" as Key,
"GSV Qty 2016",
"GSV 2016 $"

FROM "lib://QVDs/[BM Data.QVD]"
(qvd);

[BW Waranty Report]:
LOAD
"Entity - L5 Commercial Sub Region"&'_'&
"Entity - L6 GM Region"&'_'&
"Prod - Material"&'_'&
"Product – Manufacturer"&'_'&
"Calendar - Fiscal Year"&'_'&
"Calendar - Fiscal Period" as Key,
"Total Qty",
"Overall Std Cost"
FROM "lib://QVDs/[BW Waranty Report.QVD]"
(qvd);

[BW S&M Report-2013]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"GSV Qty 2013",
"GSV 2013"
FROM "lib://QVDs/[BW S&M Report-2013.QVD]"
(qvd);

[BW S&M Report-2014]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"GSV Qty 2014",
"GSV 2014"
FROM "lib://QVDs/[BW S&M Report-2014.QVD]"
(qvd);

[BW S&M Report-2015]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"GSV Qty 2015",
"GSV 2015"
FROM "lib://QVDs/[BW S&M Report-2015.QVD]"
(qvd);

[BW S&M Report-2016]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"GSV Qty 2016" as GSV_QTY_2016,
"GSV 2016"
FROM "lib://QVDs/[BW S&M Report-2016.QVD]"
(qvd);

[BW Warranty Report-2013]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2013.QVD]"
(qvd);

[BW Warranty Report-2014]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2014.QVD]"
(qvd);

[BW Warranty Report-2015]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2015.QVD]"
(qvd);

[BW Warranty Report-2016]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2016.QVD]"
(qvd);

[China Tool Data]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[China Tool Data.QVD]"
(qvd);

[Material_Master Data]:
LOAD
Catalog_Number,
"Material Description",
"Material Type",
"Material Group",
"Product Hierarchy",
SBU,
"SBU Description",
"Product Division",
"Product Div Desc",
Family,
"Product Family",
"Group",
"Group Description",
"Sub Group",
"Sub Group Description",
"Sub Sub Group",
"Sub Sub Group Desc.",
Division,
"Product Brand",
"Unit per Pak",
"Unit per KAR",
FamilyDesc,
New_ProductDivision
FROM "lib://QVDs/[Material_Master Data.QVD]"
(qvd);

This is my Data Model Viewer

Could you please help me.

[arethaking]

Do you know what is concatenate function do?

SMReport:
BW S&M Report-2013.QVD
Concatenate
BW S&M Report-2014.QVD
Concatenate
BW S&M Report-2015.QVD
Concatenate
BW S&M Report-2016.QVD

Warranty_Report:
BW Warranty Report-2014.QVD
Concatenate
BW Warranty Report-2015.QVD
Concatenate
BW Warranty Report-2016.QVD

send the script and datamodel screen

[jonesbrown]

How to Remove Synthetic Tables

This is my Script Data

[BM Data]:
LOAD
"Entity - L5 Commercial Sub Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"GSV Qty 2015",
"GSV 2015"
FROM "lib://QVDs/[BM Data.QVD]"
(qvd);

Concatenate

[BW S&M Report-2013]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"GSV Qty 2013",
"GSV 2013"
FROM "lib://QVDs/[BW S&M Report-2013.QVD]"
(qvd);

Concatenate

[BW S&M Report-2014]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"GSV Qty 2014",
"GSV 2014"
FROM "lib://QVDs/[BW S&M Report-2014.QVD]"
(qvd);

Concatenate

[BW S&M Report-2015]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"GSV Qty 2015",
"GSV 2015"
FROM "lib://QVDs/[BW S&M Report-2015.QVD]"
(qvd);

Concatenate

[BW S&M Report-2016]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"GSV Qty 2016",
"GSV 2016"
FROM "lib://QVDs/[BW S&M Report-2016.QVD]"
(qvd);

[BW Waranty Report]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Prod - Material",
"Product – Manufacturer",
"Calendar - Fiscal Year",
"Calendar - Fiscal Period",
"Total Qty" ,
"Overall Std Cost"
FROM "lib://QVDs/[BW Waranty Report.QVD]"
(qvd);

Concatenate

[BW Warranty Report-2013]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2013.QVD]"
(qvd);

Concatenate

[BW Warranty Report-2014]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2014.QVD]"
(qvd);

Concatenate

[BW Warranty Report-2015]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2015.QVD]"
(qvd);

Concatenate

[BW Warranty Report-2016]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[BW Warranty Report-2016.QVD]"
(qvd);

Concatenate

[China Tool Data]:
LOAD
"Entity - L5 Commercial Sub Region",
"Entity - L6 GM Region",
"Service Class",
"Faulty Tool",
"Calendar - Fiscal Quarter",
"Calendar - Fiscal Period",
"Tool Qty",
"Overall Std Cost"
FROM "lib://QVDs/[China Tool Data.QVD]"
(qvd);

[Material_Master Data]:
LOAD
Catalog_Number,
"Material Description",
"Material Type",
"Material Group",
"Product Hierarchy",
SBU,
"SBU Description",
"Product Division",
"Product Div Desc",
Family,
"Product Family",
"Group",
"Group Description",
"Sub Group",
"Sub Group Description",
"Sub Sub Group",
"Sub Sub Group Desc.",
Division,
"Product Brand",
"Unit per Pak",
"Unit per KAR",
FamilyDesc,
New_ProductDivision
FROM "lib://QVDs/[Material_Master Data.QVD]"
(qvd);

This is my Data Model Viewer

Could you please help me.