topic Re: Join to Oracle in QlikView

Join to Oracle

d4rlie891 — Thu, 27 Apr 2017 03:01:22 GMT

Hi,

Anybody can help me, how to do Inner Join to Oracle without loading all record first.

for example I have script like this :

GET_ALL_INVESTOR:

LOAD

"@1:20" as INV_SID,

"@23:42" as INV_SEC_ACC,

"@45:49" as INV_AB_ID,

"@104:303" as INV_NAME,

'0' & "@360:399" as INV_ACCT_NO,

"@402:415" as INV_ACCOUNT_STATUS

FROM [lib://MIS_Folder/Dashboard_SCM\Investor_All.txt]

(fix, codepage is 1252, no labels, header is 16 lines)

where Match("@402:415", 'CLOSE', 'OPEN');

inner Join(GET_ALL_INVESTOR)

Load CUST_NO,

ACCT_CARD_NO as INV_ACCT_NO,

ACCT_NAME,

ACCT_STATUS;

SELECT a.cust_no, a.acct_card_no, a.acct_name, a.acct_status

FROM ACCT_DIM a;

There are 50 million records in table Acct_Dim.

Right now, it looks like Qliksense load 50 million records to memory and then start doing inner Join.

Is there any way to load only the data from ACCT_DIM where ACCT_CARD_NO in file Investor_All.txt ?

Thanks

Re: Join to Oracle

prma7799 — Thu, 27 Apr 2017 03:39:43 GMT

Try this ..

GET_ALL_INVESTOR:

LOAD

"@1:20" as INV_SID,

"@23:42" as INV_SEC_ACC,

"@45:49" as INV_AB_ID,

"@104:303" as INV_NAME,

'0' & "@360:399" as INV_ACCT_NO,

"@402:415" as INV_ACCOUNT_STATUS

FROM [lib://MIS_Folder/Dashboard_SCM\Investor_All.txt]

(fix, codepage is 1252, no labels, header is 16 lines)

where Match("@402:415", 'CLOSE', 'OPEN');

inner Join(GET_ALL_INVESTOR)

Load CUST_NO,

ACCT_CARD_NO as INV_ACCT_NO,

ACCT_NAME,

ACCT_STATUS;

SELECT a.cust_no, a.acct_card_no, a.acct_name, a.acct_status

FROM ACCT_DIM a

where a.ACCT_CARD_NO in ('Investor_All.txt');

Thanks.

Re: Join to Oracle

Anil_Babu_Samineni — Thu, 27 Apr 2017 04:45:18 GMT

Your script ok to me but thing is you don't have Common field from two tables

PS: Inner Join - The keyword selects records that have matching values in both tables on common field

Re: Join to Oracle

d4rlie891 — Thu, 27 Apr 2017 05:25:23 GMT

Hi Anil,

This are records in Investor_All1.txt

ACCT_CARD_NO

04581339041

04582614788

and I try run this script :

Load CUST_NO,

ACCT_CARD_NO;

SELECT a.cust_no, a.acct_card_no

FROM ACCT_DIM a

where a.acct_card_no in ('Investor_All1.txt');

Why The result is 0?

Lines fetched: 0

Creating search index

Re: Join to Oracle

rahulpawarb — Thu, 27 Apr 2017 05:27:20 GMT

Hello Darmawan,

If you want to filter the records at database level then apply the INNER JOIN or EXISTS conditions at database level.

//In both the cases table Investor_All must have to be there in database

LOAD CUST_NO,

ACCT_CARD_NO as INV_ACCT_NO,

ACCT_NAME,

ACCT_STATUS;

SELECT a.cust_no,

a.acct_card_no,

a.acct_name,

a.acct_status

FROM ACCT_DIM a

WHERE NOT EXISTS (SELECT 1

FROM Investor_All

WHERE Investor_All.INV_ACCT_NO = a.acct_card_no);

//OR

LOAD CUST_NO,

ACCT_CARD_NO as INV_ACCT_NO,

ACCT_NAME,

ACCT_STATUS;

SELECT a.cust_no,

a.acct_card_no,

a.acct_name,

a.acct_status

FROM ACCT_DIM a

INNER JOIN Investor_All ON Investor_All.INV_ACCT_NO = a.acct_card_no;

Hope this will be helpful.

Thank you!

Rahul

Re: Join to Oracle

Anil_Babu_Samineni — Thu, 27 Apr 2017 05:30:50 GMT

Try with Exists function in Qlikview and then Inner Join with Match fields from 2 tables

Re: Join to Oracle

rahulpawarb — Thu, 27 Apr 2017 05:34:13 GMT

Hello Darmawan,

where a.acct_card_no in ('Investor_All1.txt'); is trying to fetch records where a.acct_card_no is equal to 'Investor_All1.txt' ; which is not true in your case. If you want to do record filtering at database level then push Investor_All1.txt file to database level and perform actions mentioned in my earlier post.

Regards!

Rahul

Re: Join to Oracle

d4rlie891 — Thu, 27 Apr 2017 05:42:51 GMT

HI Rahul,

I see, so there is no other way to do this

Thanks

Re: Join to Oracle

rahulpawarb — Thu, 27 Apr 2017 05:52:09 GMT

At database level there is no other workaround (May be try this - Use CSV file as External table : CSV Table « Table « Oracle PL / SQL).

However, if you want to apply this logic at QlikView side then use the Exists function mentioned by Anil (refer attached sample application).

Hope this will be helpful.

Regards!

Rahul

Re: Join to Oracle

d4rlie891 — Thu, 27 Apr 2017 06:59:46 GMT

Thanks Rahul for you information.

I think it works !

Re: Join to Oracle

rahulpawarb — Thu, 27 Apr 2017 07:05:49 GMT

Cheers,

Rahul