Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
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
Highlighted
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

View solution in original post

10 Replies
Highlighted
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.

Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
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

Highlighted
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

Highlighted

Re: Join to Oracle

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
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

Highlighted
New Contributor III

Re: Join to Oracle

HI Rahul,

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


Thanks

Highlighted
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

View solution in original post

Highlighted
New Contributor III

Re: Join to Oracle

Thanks Rahul for you information.

I think it works !