Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What output do you expect?
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
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?
Just forget about the Dept and month. It should be applied to PRD_ID only once
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
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.
Is this a presentation requirement or do you really want to build your data model like this?
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