Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, I am connected to SQL server and have 10 tables in the database. how can i manually assign join in edit script as it creates several synthetic keys creating confusion in table viewer.
For the attached diagram, is it possible to remove all the synthetic keys?
and below is the script used:
achvmnt:
LOAD "ACHIEVEMENT_WID",
"EARNED_AT_DT",
"EARNED_AT_DT_WID",
"ROW_WID",
"USER_WID";
SQL SELECT "ACHIEVEMENT_WID",
"EARNED_AT_DT",
"EARNED_AT_DT_WID",
"ROW_WID",
"USER_WID"
FROM "HealthScore_DW".HealthScore."W_ACHIEVEMENT_F";
join(achvmnt)
LOAD "ACHIEVEMENT_CATEGORY",
"ACHIEVEMENT_CATEGORY_ID",
"ACHIEVEMENT_TYPE",
"ACHIEVEMENT_TYPE_ID",
"ROW_WID" as "ACHIEVEMENT_WID";
SQL SELECT "ACHIEVEMENT_CATEGORY",
"ACHIEVEMENT_CATEGORY_ID",
"ACHIEVEMENT_TYPE",
"ACHIEVEMENT_TYPE_ID",
"ROW_WID"
FROM "HealthScore_DW".HealthScore."W_ACHIEVEMENT_D";
LOAD "CAL_DATE" as "EARNED_AT_DT_WID",
"CAL_MONTH",
"CAL_QTR",
"CAL_WEEK",
"CAL_YEAR",
"MONTH_NAME",
"MONTH_WID",
"MONTH_YR_NAME",
"ROW_WID" as t.row_wid;
SQL SELECT "CAL_DATE",
"CAL_MONTH",
"CAL_QTR",
"CAL_WEEK",
"CAL_YEAR",
"MONTH_NAME",
"MONTH_WID",
"MONTH_YR_NAME",
"ROW_WID"
FROM "HealthScore_DW".HealthScore."W_TIME_D";
LOAD
"FIRST_NAME",
"REG_DT_WID" as "t.row_wid",
"ROW_WID" as "USER_WID",
SEGMENT,
SIGNUPDATE as "EARNED_AT_DT_WID";
SQL SELECT "FIRST_NAME",
"REG_DT_WID",
"ROW_WID",
SEGMENT,
SIGNUPDATE
FROM "HealthScore_DW".HealthScore."W_USER_D";
Hi Aditi,
Please see below example:
ABC:
LOAD
XXX,
AAA_NUM,
STATUS_CODE,
AAA_DATE,
AAA_SET_ID;
SQL SELECT
XXX,
AAA_NUM,
STATUS_CODE,
AAA_DATE,
AAA_SET_ID
FROM table1 WHERE STATUS_CODE <>'5' and XXX = ($(XXX))
AND TO_CHAR(AAA_DATE, 'YYYY') IN ($(ActiveYears));
left join(ABC)
LOAD
OUR_PROPORTION,
AAA_SET_ID,
TRANSACTION_DATE,
PAYMENT_METHOD;
SQL SELECT OUR_PROPORTION,
AAA_SET_ID,
TRANSACTION_DATE,
PAYMENT_METHOD FROM table2 WHERE
TO_CHAR(TRANSACTION_DATE, 'YYYY') IN ($(ActiveYears));
After the join the final table would be named ABC, u can use some meaningful name instead of this
Except this,i would suggest that u create 2 qvds first having all the fields from both the tables w/o the where clause as it would prevent hitting ur transaction table every time you reload & also if u want to add some more fields from the same tables you can simply have them from the qvd.
After creating the qvd take only the fields required here with the where clause from the qvd to perform the join.
Thanks,
AS
Hi Aditi,
You need to load only the necessary columns which you are using in the Dashboard instead of loading all. Also if the column names are same then you have to rename or form a key or if the data is same then you have to concatenate the tables etc. this are the various ways of eliminating Synthetic keys.
For example:
Transaction:
LOAD
Sales,
CustomerID,
SalesmanID
FROM Transaction;
Salesman:
LOAD
SalesmanID,
FirstName,
LastName,
City,
Country,
Phone
FROM Salesman;
Customer:
LOAD
CustomerID,
FirstName,
LastName,
City,
Country,
Phone
FROM Customer;
When you load above the synthetic keys are formed between customer and Salesman tables, to avoid just rename the columns like below.
Salesman:
LOAD
SalesmanID,
FirstName AS SalesmanFirstName,
LastName AS SalesmanLastName,
City AS SalesmanCity,
Country AS SalesmanCountry,
Phone AS SalesmanPhone
FROM Salesman;
Customer:
LOAD
CustomerID,
FirstName AS CustomerFirstName,
LastName AS CustomerLastName,
City AS CustomerCity,
Country AS CustomerCountry,
Phone AS CustomerPhone
FROM Customer;
Generally phone numbers are not used in Dashboard, so you can remove it. Hope this helps you.
Regards,
Jagan.
hi, i am having 15- 20 tables, so how would i be able to join those, cant i write in custom sql like in tableau
above is the procedure just to avoid synthetic keys formation. how would i be able to join these three tables? as after renaming the column names, it wont have any join in between them
Hi,
In Qlikview you have to design the datamodel, tables are joined based on the column names automatically, so you have to manually rename the columns to create the datamodel. In the above example the transaction table is joined to Customer table by using the CustomerID column and Salesman table by using the SalesmanID column.
Hope this helps you.
Regards,
Jagan.