Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Table Join

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

8 Replies
simenkg
Specialist
Specialist

What output do you expect?

upaliwije
Creator II
Creator II
Author

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

nagaiank
Specialist III
Specialist III

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?

upaliwije
Creator II
Creator II
Author

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

Not applicable

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             

PrashantSangle

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.

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
MarcoWedel

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

qlikoqlik
Creator
Creator

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