topic Re: Dimension with no unique key in QlikView

Dimension with no unique key

Mon, 05 Nov 2012 06:44:50 GMT

i have a dimension without a unique key.

say for example, in Customer dimension, one customer can belong to many REP’s in same State.

how can I join this dimension with my Transactions fact table?

Ex Customer Table:

Customer1,State1,REP1

Customer1,State1,REP2

Customer1,State1,REP3…..

so, any ideas to generate a unique key in this scenario?

Re: Dimension with no unique key

sushil353 — Mon, 05 Nov 2012 06:58:52 GMT

Hi,

Can u provide structure for your FACT's and DIMENTIONS

HTH

Sushil

Re: Dimension with no unique key

whiteline — Mon, 05 Nov 2012 07:29:12 GMT

Hi.

You could use AutonumberHash256 function to generate unique id:

AutonumberHash256(Customer, REP) as IDCustomerREP

Re: Dimension with no unique key

Mon, 05 Nov 2012 21:57:48 GMT

Hi Sushil & Whiteline,

i think, i didn't explain the scenario properly.

I do have Customer & State fields in sales fact table.

i need to connect this Sales table to Customer dimension by depending on those fields.

Thanks for your help.

Re: Dimension with no unique key

MayilVahanan — Mon, 05 Nov 2012 22:23:07 GMT

What are the fields in sales table??

Re: Dimension with no unique key

Mon, 05 Nov 2012 22:26:02 GMT

Hi ramasamy,

Sales:

SO No,Customer, State,Revenue.

Customer:

Customer,State,Rep.

Thanks for ur reply.

Re: Dimension with no unique key

MayilVahanan — Mon, 05 Nov 2012 22:37:44 GMT

Then customer is common field to each other. so they automatically combine with each other but change the state field name as saleState. Otherwise, it form loop or synthetic key..

Try like this

Load customer&''&State as Key,customer as salesCutomer, state as salesState, revenue,soNo from sales;

Load customer&state as Key,state, customer ,rep from customertable where exists(Key,Key);

//by this,second table load only if key exists in the first table only..

see this link:

http://www.quickqlearqool.nl/?p=910

for join.

Hope it helps

Re: Dimension with no unique key

Mon, 05 Nov 2012 22:43:18 GMT

but the problem is customer&state as Key is not a unique key in Customer dimension.

one customer can have many rep's for same state.

Ex Customer Table:

Customer1,State1,REP1

Customer1,State1,REP2

Customer1,State1,REP3…..

Re: Dimension with no unique key

MayilVahanan — Mon, 05 Nov 2012 23:14:47 GMT

then u tey first approach..make customer field as common to both tsble n rename state field.. How u expecting

The result..can u post a sample file n expecting result..by that can easy to solve ur requirement..

Join , inner join, left join, concantenate concept also use to combine tables but without kowing the expected result its difficult to find

Re: Dimension with no unique key

erichshiino — Tue, 06 Nov 2012 22:07:24 GMT

I might be missing something but given a sales order it's not possible to tell the right sales rep.

Using Mayil approach, you can create a more complex key, using Customer & '|' & State & '|' & Rep as Key, but there would be no link to the sales, right?

If you provide us some data samples, we can better understand the problem.

Regards,

Erich

Re: Dimension with no unique key

Wed, 07 Nov 2012 05:23:02 GMT

say for example:

LOAD

* INLINE

[SO, Customer, State, Revenue

0,customer1,State1,15

1,customer1,State1,25

2,customer2,State1,45

3,customer1,State2,35];

LOAD * INLINE

[Customer, State, REP

customer1,State1,REP1,

customer1,State1,REP2];

my desired output would be:

Customer,State,REP

customer1,State1,REP1

customer1,State1,REP2

Re: Dimension with no unique key

jagan — Wed, 07 Nov 2012 06:54:07 GMT

Hi,

Try this script, you will get the desired output, but the records will be duplicated when you use SO and Revenue dimensions because many-many mapping between these tables. Check attached file.

LOAD

Customer & '-' & State AS Key,

INLINE

[SO, Customer, State, Revenue

0,customer1,State1,15

1,customer1,State1,25

2,customer2,State1,45

3,customer1,State2,35];

INNER JOIN

LOAD

Customer & '-' & State AS Key,

REP

INLINE

[Customer, State, REP

customer1,State1,REP1,

customer1,State1,REP2];

Hope this helps you.

Regards,

Jagan.