topic Where exist different connections in QlikView

Where exist different connections

Michiel_QV_Fan — Thu, 08 Oct 2009 18:30:09 GMT

Hi,

Is it possible tu use where exist with different data sources.

For instance:

Specs:

from ..../...../......xls

Orders:

Product,

Quantity

from {datasource} where exists A;

If not, any idea how limit Orders with where exists?

Thanks!

Where exist different connections

Thu, 08 Oct 2009 18:41:48 GMT

Orders:

Product,

Quantity

from {datasource} where exists(A, A);

which means only load rows from 'datasource' where the value of 'A' has already been loaded into 'A' (which means into 'Specs')

Regards,

Gordon

Where exist different connections

Michiel_QV_Fan — Thu, 08 Oct 2009 18:46:24 GMT

SQL error: Syntax error (comma) in query expression 'exists(DATE, DATE)'.
Script line: SELECT * FROM txBilling where exists(DATE, DATE)
SQL SELECT * FROM txBilling where exists(DATE, DATE)

This is error the error I get all the time.

This is the script line:

SQL SELECT * FROM txBilling where exists(DATE, DATE);

in which where and exists are not highlighted in blue

Where exist different connections

biester — Thu, 08 Oct 2009 19:12:17 GMT

I should say, the 'where exists(FIELD,EXPRESSION) ' in this form can only be used with LOAD, not with an SQL SELECT; the database you are querying does not know this form of where exists, hence the SQL syntax error

Rgds,
Joachim

Where exist different connections

Michiel_QV_Fan — Thu, 08 Oct 2009 19:45:53 GMT

Joachim,

Do you have an example what statement to use with sql select?

Where exist different connections

biester — Thu, 08 Oct 2009 19:51:53 GMT

With SQL SELECT you can only use what the underlying database understands. The database of course does not know anything about tables in QlikView, so in the SQL SELECT itself there is no possibility to refer to them. You all have to do it with load. A possibility would be to store date into qvd and then load from qvd; THERE you can use where exists.

Rgds,
Joachim

Where exist different connections

Michiel_QV_Fan — Thu, 08 Oct 2009 19:56:41 GMT

Thanks that a clear explanation for me.

The search continues for me.

Where exist different connections

biester — Thu, 08 Oct 2009 20:04:29 GMT

What search? If I get you right I think what you want to achieve is possible without any problems.

e. g.:

Specs:

Load A,B from ..xls;

Orders_Temp:
SQL Select Product,A as A_Temp,Quantity from {Database};

Orders:
LOAD * resident Orders_Temp where exists(A,A_Temp);

drop table Orders_Temp;

Just a rough sketch without trying it, but it should work with more or less modifications in your case.

Rgds,
Joachim

Where exist different connections

Michiel_QV_Fan — Thu, 08 Oct 2009 20:33:22 GMT

Search -> still learning qlikview. To be the first Certified Qlikview Consultant in the company for which I work

Where exist different connections

biester — Thu, 08 Oct 2009 20:34:51 GMT

Ah, ok, I understand!

Wish you all the best!

Where exist different connections

Michiel_QV_Fan — Thu, 08 Oct 2009 20:42:24 GMT

Thanks for your help.

I'm figuring out your script. Haven't got it working yet. It reads the temp table twice and not the RESIDENT table....

Where exist different connections

Michiel_QV_Fan — Thu, 08 Oct 2009 20:43:59 GMT

txBilling_temp:
LOAD
ACCTNUM,
AMTPAID,
BILL,
BILLREF,
DATE,
INVOICE,
isAccountInvoiced,
MEDNO,
ReDone;
SQL SELECT * FROM txBilling;

txBilling:
LOAD * RESIDENT txBilling_temp;

drop table txBilling_temp;

Results in nothing....

Where exist different connections

Anonymous — Thu, 08 Oct 2009 21:04:30 GMT

HI,

QV will auto concatenate if the two adjacent tables have the same columns. Place the word NOCONCATENATE between the two loads to stop this. You will then be able to drop your temp table withouth losing your resident table.

Also, looking at your exists issues.....you can always use left or inner join to overcome your problem. Join the two tables on your DATE field. so.....as a noddy script example.....

Load Date
SQL SELECT .......

left join

Load Date,
col2,
col3,
..............................

This would only give you dates in your second table for dates in your first. etc. etc.

Cheers.

Ad.