topic Re: Joining tables in QlikView

Joining tables

Wed, 01 Jun 2011 09:27:42 GMT

Hello all

Ok, I have a question about joining some tables.

I have following tables.

fact:

productN, product type, orderN, customerN

321654, A, 50, 132

987654, B, 20, 423

654987, C, 13, 512

so on. I have 2 tables contains information for product A B and others.

type_AB:

orderN, information

type_other:

customerN, information

how do I join them? Feel free for any suggest.

Regards.

Muncho

Re: Joining tables

Wed, 01 Jun 2011 09:36:12 GMT

Ok, now im doing like this.

Separate my fact table to 2 table for product types

product_ab:

load * resident fact

where [product type]='A' or [product type]='B'

product_other:

load * resident fact

where not [product type]='A' or [product type]='B'

Then i joined my information tables for seperated fact tables. And concatenate them to build my fact table.

But after all i got some duplicated rows.

Any suggest?

Re: Joining tables

Wed, 01 Jun 2011 11:07:21 GMT

I found that where not is not working right. in product_other table there is product B inside.

so where not [product type]='A' or [product type]='B' is wrong.

Anyone pls correct this.

Re: Joining tables

Wed, 01 Jun 2011 11:11:34 GMT

Ok, I fount.

where where [product type]<>'A' and [product type]<>'B'

now it's looking better

Re: Joining tables

nagaiank — Wed, 01 Jun 2011 11:13:31 GMT

Try

where not ([product type] = 'A' or [product type] = 'B')

where not([product type] = 'A' and not([product type] = 'B')

Re: Joining tables

Wed, 01 Jun 2011 11:22:44 GMT

thanks krishna.

where clause working fine now.

Let's back to business, Is there any better solution of how to join them?

Re: Joining tables

Wed, 01 Jun 2011 11:28:04 GMT

Hi Muncho

What is your expected end result. What you've described so far is very confusing.

/Max

Re: Joining tables

Wed, 01 Jun 2011 11:42:15 GMT

Hello Max,

Isn't that obvious in my first post? if not, i'm really sorry about my bad English.

It is not real data. It's just sample.

i have a fact table. And i wanna join information table.

This type_AB table is for where product type A and B, key field is orderN.

Type_other table is for where product type<> A and B, key field is customerN

My second post is what i'm trying to do this. And i gor problem with where clause bla bla bla...

I just wanna know is there better solution how to join them? That's all

Regard

Re: Joining tables

Wed, 01 Jun 2011 13:14:56 GMT

Why would you need to make separate loads of your facts table?

Can you maybe post a sample QVW instead?

Cheers

Re: Joining tables

Wed, 01 Jun 2011 13:37:14 GMT

I'm sort of new in QV and separating fact table is just my idea.

If you have better idea then feel free to post.

Re: Joining tables

Wed, 01 Jun 2011 13:42:03 GMT

As I don't know the purpose It's very hard to help out.

let me get this straight - You need to join two information tables to your facts table. Your keys are orderN and customerN. Do you also need product type in the key?

Re: Joining tables

Wed, 01 Jun 2011 13:58:59 GMT

i have 2 information table. first information table have orderN key field and it should join where product type A and B in fact table. Second table should join where not product type A and B. key is customerN.

Hope you got it know.

Re: Joining tables

vincent_ardiet — Wed, 01 Jun 2011 14:27:59 GMT

Hi Muncho,

If I undestand what your are trying to do :

- Rename in your fact table orderN and customerN (example orderN_origin and customerN_origin) ;

- Add in fact a calculated column named orderN as : if(match([product type],'A','B')>0,orderN_origin) ;

- Add in fact a calculated column named customerN as : if(match([product type],'A','B')=0,customerN_origin) ;

You will obtain

productN, product type, orderN, customerN, orderN_origin, customerN_origin

321654, A, 50, , 50, 132

987654, B, 20, , 20, 423

654987, C, , 13, 13 , 512

Then you can join your tables.

Regards,

Vincent

Re: Joining tables

Wed, 01 Jun 2011 14:54:02 GMT

Hello Vincent,

Ok. I got your your idea.

Thank you very much ^^

Re: Joining tables

Thu, 02 Jun 2011 01:24:14 GMT

See if this sample can help you.