topic Re: Exsists function in QlikView

Exsists function

Fri, 23 Sep 2016 07:56:58 GMT

Hello everyone,

In my load data model i had a fact and two dimension tables, one for Customer and other of Ok_Customer. Both of these have customer ID in them.

I use to load

Fact:

Customer_Key

Ok_Customer_Key

Metrics

Dim1:

Customer_key

other details

where exists(Customer_key)

Dim2:

Ok_Customer_Key

other details

where exists(Ok_Customer_key)

Now i want to combine dim1 and dim2 . So how can i use exists function?

Re: Exsists function

Chanty4u — Fri, 23 Sep 2016 07:59:32 GMT

Hi,

What are the values for Customerkey and OK cust key?

Re: Exsists function

Fri, 23 Sep 2016 08:03:41 GMT

Does it matter?

These are unique keys associated to customers and Ok_customers.

Re: Exsists function

Anonymous — Fri, 23 Sep 2016 08:05:26 GMT

what do you mean Combine? concatenate, join?

concatenate (Dim1)

load

Ok_Customer_Key

other details

where exists(Customer_key,Ok_Customer_key)

join automatically only joins same records

left join(Dim1)

load

Ok_Customer_Key as Customer_Key

Ok_Customer_Key

other Details

resident Dim2

Re: Exsists function

Fri, 23 Sep 2016 08:08:02 GMT

Sorry forgot to mention.

Combine means

Dim1:

Customer_key

other details

Join

Dim2:

Ok_Customer_Key

other details

Re: Exsists function

Fri, 23 Sep 2016 08:12:02 GMT

i m doing full outer join

Re: Exsists function

Anonymous — Fri, 23 Sep 2016 08:12:06 GMT

this should do (Keep origin OK_Customer_Key, so you can identify those records coming from Dim2)

left join(Dim1)

load

Ok_Customer_Key as Customer_Key

Ok_Customer_Key

ther Details

resident Dim2

above script will only load those records from Dim2 which exists (Customer_Key)

Re: Exsists function

Fri, 23 Sep 2016 08:15:06 GMT

what if i just want to load

Dim1

join

Dim2

where

exsists (Customer_Key,Ok_Customer_Key)

Will the above work correctly?

Re: Exsists function

Anonymous — Fri, 23 Sep 2016 08:20:06 GMT

at least you need an identical field, without you would create a cartesian product

but the where condition is obsolete as you only want the records joined where Ok_Customer_Key exists as Customer_Key

so your Syntax would look like

left join(Dim1)

load

Ok_Customer_Key as Customer_Key

Ok_Customer_Key

other Details

resident Dim2

where exists(Customer_Key, OK_Customer_Key)

-> Where condition is not neccessary!

Re: Exsists function

Fri, 23 Sep 2016 08:22:30 GMT

I have an identical field already have customer_cd which is present in both the tables.

Re: Exsists function

Anonymous — Fri, 23 Sep 2016 08:26:29 GMT

then the following Syntax should do as Long as field customer_cd is in both tables DIm1 and DIm2

left join(Dim1)

load

Ok_Customer_Key

other Details

resident Dim2

where exists(Customer_Key, OK_Customer_Key)

Re: Exsists function

Fri, 23 Sep 2016 08:49:37 GMT

What if i have already placed

Dim 1

join

Dim 2

in a qvd .

Now i want to use it in presentation layer with exists function?

Re: Exsists function

Fri, 23 Sep 2016 08:53:25 GMT

Can i use :

Load * from QVD where exists(Customer_Key) or exists(Ok_Customer_Key);??

Re: Exsists function

Anonymous — Fri, 23 Sep 2016 08:54:38 GMT

can you explain more in Detail? what do you mean with presentation layer?

normally you would provide the datamodel in an own qvw and load it binary

in surface

what Special do you want to perfrom in presentation layer?

Re: Exsists function

Fri, 23 Sep 2016 08:58:21 GMT

You are correct that i should load in data model and do binary.

But for testing purpose i was doing it like this.

I just want to know whether if i use:

Load * from QVD

where

exists(Customer_Key) or exists(Ok_Customer_Key);??

Is the above approach correct?

Exsists function

Re: Exsists function

Anonymous — Fri, 23 Sep 2016 09:17:36 GMT

it's okay for testing purposes (i do that as well)

Load * from QVD

where

exists(Customer_Key,Ok_Customer_Key);