Solved: Joining tables - Qlik Community

Report Inappropriate Content · ‎2011-06-01

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

vincent_ardiet · ‎2011-06-01

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

View solution in original post

Report Inappropriate Content · ‎2011-06-01

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?

Report Inappropriate Content · ‎2011-06-01

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.

Report Inappropriate Content · ‎2011-06-01

Ok, I fount.

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

now it's looking better

nagaiank · ‎2011-06-01

Try

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

or

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

Report Inappropriate Content · ‎2011-06-01

thanks krishna.

where clause working fine now.

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

Report Inappropriate Content · ‎2011-06-01

Hi Muncho

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

/Max

Report Inappropriate Content · ‎2011-06-01

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

Report Inappropriate Content · ‎2011-06-01

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

Can you maybe post a sample QVW instead?

Cheers

Report Inappropriate Content · ‎2011-06-01

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.

Joining tables

Related Topics