topic Re: Inner Join in Load SQL Load Statement in QlikView

Inner Join in Load SQL Load Statement

Tue, 09 Aug 2016 21:11:24 GMT

Hi All,

I'm am running into some trouble with my inner join statement. I am bringing in the correct number of observations from 'Table 1' but when inner joining using syntax below, Qlik is bringing in all records from the other tables, not only where the tables are mutual...

Suggestions? Thank you!

SQL SELECT ITMSNumber,Prioritization,SCA,DATALOADSTATUSID, LOADPRIORITYSTATUSID
FROM MPL.dbo.tblSTRATEGY
where loadprioritystatusid=20 or loadprioritystatusid=30 or loadprioritystatusid=100;

Inner Join(Table1)
SQL SELECT *
FROM MPL.dbo.tblAPPINVENTORY;

Inner Join(Table1)
SQL SELECT Acronym,
ITMSNumber,
Name
FROM MPL.dbo.tblBASIC;

Re: Inner Join in Load SQL Load Statement

maxgro — Tue, 09 Aug 2016 21:48:39 GMT

some suggestions:

stop the script after the first inner join: do you get the expected result?

which fields are you using for joining the first 2 tables? are the values of these fields duplicated?

Inner Join(Table1)
SQL SELECT *
FROM MPL.dbo.tblAPPINVENTORY;

exit script;

Re: Inner Join in Load SQL Load Statement

Tue, 09 Aug 2016 22:02:16 GMT

Hi Massimo,

I did not get the expected results when stopping the script after the first join...

All the tables that I am joining together have a common field 'ITMSNumber' that I am looking to join on. The values in each of these tables are distinct, no duplicate entries in either table.

Re: Inner Join in Load SQL Load Statement

Clever_Anjos — Tue, 09 Aug 2016 22:14:03 GMT

As all your tables resides into same database I would use the SQL join, not the Qlik.

It would perform much better

SELECT ITMSNumber,Prioritization,SCA,DATALOADSTATUSID, LOADPRIORITYSTATUSID, morefields

FROM MPL.dbo.tblSTRATEGY a

inner join MPL.dbo.tblAPPINVENTORY b on ( join clauses goes here)

inner join MPL.dbo.tblBASIC c on ( join clauses goes here)

where loadprioritystatusid in (20,30,100);

Re: Inner Join in Load SQL Load Statement

maxgro — Tue, 09 Aug 2016 22:22:40 GMT

I did not get the expected results when stopping the script after the first join.

and what's the problem?

duplicated ITMSNumber? loadprioritystatus not in (20,30,100)?

could you post the .qvw or a screenshot of the result table with the problem?