topic Re: How to Remove Synthetic Tables in Qliksense in App Development

How to Remove Synthetic Tables in Qliksense

jonesbrown — Sat, 16 Nov 2024 19:07:19 GMT

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.

Re: How to Remove Synthetic Tables in Qliksense

jochem_zw — Thu, 13 Feb 2020 15:41:48 GMT

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/synthetic-keys.htm

Suc6

Re: How to Remove Synthetic Tables in Qliksense

jonesbrown — Fri, 14 Feb 2020 03:45:07 GMT

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.

Re: How to Remove Synthetic Tables in Qliksense

arethaking — Fri, 14 Feb 2020 04:26:54 GMT

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

Re: How to Remove Synthetic Tables in Qliksense

jonesbrown — Fri, 14 Feb 2020 05:28:57 GMT

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-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

This is my Data Model Viewer

Could you please help me.