topic Re: Data Load Query in QlikView

Data Load Query

mccook — Wed, 06 Feb 2013 09:29:14 GMT

Hi,

I'm new to Qlikview and I was wondering if at the point of loading my data, can I join to another table to restrict the amount of data I load.

e.g. I have a list of names in table A, Customers, suppliers, employees etc, which has over 500,000 lines, however table A doesn't actually tell me which ones are customers or suppliers etc. This information is held in table B (there is an account number link between the two tables). I only need 10,000 of the records from table A (the customers).

Can I load only these 10,000 into Qlikview without having to load all of table A and all of table B where they are customers and then joining them together?

Thanks,

Dean

Re: Data Load Query

sujeetsingh — Wed, 06 Feb 2013 09:36:19 GMT

Yes sure if you are working in sql u can handle it in sql well.

Re: Data Load Query

mccook — Wed, 06 Feb 2013 09:58:46 GMT

Thanks,

So I've made up an example, how can I amend this to achieve what I want:

// ======= Names =================
Names:
LOAD

Name,
Number;
SQL SELECT *
FROM Table A;

STORE Names into "Names".QVD;

DROP TABLE Names;

// ======= Account Type ===========
AccountType:
Load
Type,
Number;;
SQL SELECT *
FROM Table B
Where Type = 'Customer';

STORE AccountType into "AccountType".QVD;

DROP TABLE AccountType;

Cheers,

Dean

Re: Data Load Query

rbecher — Wed, 06 Feb 2013 10:10:58 GMT

Hi Dean,

I would load Table B first and then Table A Where Exists(Number):

// ======= Account Type ===========

AccountType:

Load

Type,

Number;;

SQL SELECT *

FROM Table B

Where Type = 'Customer';

// ======= Names =================
Names:
LOAD

Name,
Number

Where Exists(Number);
SQL SELECT *
FROM Table A;

- Ralf

Re: Data Load Query

mccook — Wed, 06 Feb 2013 10:17:04 GMT

That's great,

Thanks,

Dean

Re: Data Load Query

rbecher — Wed, 06 Feb 2013 10:27:10 GMT

Of course, there is also a way to do this in SQL:

// ======= Names =================
Names:
LOAD

Name,
Number;
SQL SELECT *
FROM Table A Where Number IN (SELECT Number FROM Table B Where Type = 'Customer');

This could be more sufficient because the records doesn't need to transferred to QlikView.

- Ralf

Re: Data Load Query

mccook — Wed, 06 Feb 2013 10:35:29 GMT

Even better,

Thanks,

Dean