topic Re: loading data from multiple tables with where (join clause) in QlikView

loading data from multiple tables with where (join clause)

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: loading data from multiple tables with where (join clause)

amit_saini — Tue, 25 Feb 2014 07:27:34 GMT

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,

Re: loading data from multiple tables with where (join clause)

jagan — Tue, 25 Feb 2014 07:36:59 GMT

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.

Re: loading data from multiple tables with where (join clause)

Tue, 25 Feb 2014 09:51:57 GMT

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

Re: loading data from multiple tables with where (join clause)

Tue, 25 Feb 2014 09:53:02 GMT

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

Re: loading data from multiple tables with where (join clause)

jagan — Wed, 26 Feb 2014 02:12:54 GMT

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.