Join to Oracle

[d4rlie891]

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

[prma7799]

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.

[Anil_Babu_Samineni]

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

[d4rlie891]

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]

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

[Anil_Babu_Samineni]

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

[rahulpawarb]

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]

HI Rahul,

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

Thanks

[rahulpawarb · Accepted solution]

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]

Thanks Rahul for you information.

I think it works !