topic Re: Table Join in QlikView

Table Join

upaliwije — Sun, 10 Aug 2014 14:22:06 GMT

I have following two table in my data model

TABLE 2

PRD_ID SALES DEPT MONTH

A1 2000 X 1

B1 1000 Y 2

C1 3000 Z 3

A1 1500 Z 2

B1 4000 X 3

TABLE 2

PRD_ID RETURNS

A1 100

B1 200

C1 300

when I join above tables with PRD_ID I get the following output

PRD_ID SALES DEPT MONTH RETURNS

A1 2000 X 1 100

B1 1000 Y 2 200

C1 3000 Z 3 300

A1 1500 Z 2 100

B1 4000 X 3 200

Returns amount is duplicated for PRD-ID A1 and B1

How can I avoid this please. Pls help me

Re: Table Join

simenkg — Sun, 10 Aug 2014 14:25:27 GMT

What output do you expect?

Re: Table Join

upaliwije — Sun, 10 Aug 2014 14:30:22 GMT

PRD_ID SALES DEPT MONTH RETURNS

A1 2000 X 1 100

B1 1000 Y 2 200

C1 3000 Z 3 300

A1 1500 Z 2

B1 4000 X 3

I am expecting above output

Re: Table Join

nagaiank — Sun, 10 Aug 2014 14:44:27 GMT

Your data has two tables TABLE 1 and TABLE 2.

Where is it specified that the RETURNS of 100 is to be applied to the SALES of DEPT X and not to the SALES of DEPT Z as you expect?

Re: Table Join

upaliwije — Sun, 10 Aug 2014 14:49:23 GMT

Just forget about the Dept and month. It should be applied to PRD_ID only once

Re: Table Join

Sun, 10 Aug 2014 14:57:40 GMT

In that case you need to include Month variable in Table 2 also and join with PRD_ID and MONTH

TABLE 1

PRD_ID SALES DEPT MONTH

A1 2000 X 1

B1 1000 Y 2

C1 3000 Z 3

A1 1500 Z 2

B1 4000 X 3

TABLE 2

PRD_ID RETURNS MONTH

A1 100 1

B1 200 2

C1 300 3

OUTPUT:

PRD_ID SALES DEPT MONTH RETURNS

A1 2000 X 1 100

B1 1000 Y 2 200

C1 3000 Z 3 300

A1 1500 Z 2

B1 4000 X 3

Re: Table Join

PrashantSangle — Sun, 10 Aug 2014 16:31:26 GMT

Hi,

As per my knowledge using join it is not possible.

but you can achieve desired result in front end using AGGR()

Please find attached document.

It is as per requirement.

Re: Table Join

MarcoWedel — Mon, 11 Aug 2014 11:44:02 GMT

Is this a presentation requirement or do you really want to build your data model like this?

Re: Table Join

qlikoqlik — Mon, 11 Aug 2014 11:59:15 GMT

Hi Upali

Is your logic is returns will be shown against the earliest month, then you can do this script by using rank or peek functions

Thanks and regards

Padma