Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
d4rlie891
New Contributor III

Join to Oracle

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

1 Solution

Accepted Solutions
rahulpawarb
Valued Contributor III

Re: Join to Oracle

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

10 Replies
prma7799
Honored Contributor III

Re: Join to Oracle

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

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

Life is so rich, and we need to respect to the life !!!
d4rlie891
New Contributor III

Re: Join to Oracle

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

rahulpawarb
Valued Contributor III

Re: Join to Oracle

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

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

Life is so rich, and we need to respect to the life !!!
rahulpawarb
Valued Contributor III

Re: Join to Oracle

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

d4rlie891
New Contributor III

Re: Join to Oracle

HI Rahul,

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


Thanks

rahulpawarb
Valued Contributor III

Re: Join to Oracle

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

d4rlie891
New Contributor III

Re: Join to Oracle

Thanks Rahul for you information.

I think it works !

Community Browser