nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Duplicate Transaction Filter Issue

Nikhila reddy

Hi All,

Requirement:

Criteria:

Product - WSD Purchase

Status = Processed

Where member ID, processed date and PNR # are the same

Output:

Member #

Transaction ID

Amount

Processed date

This is the report showing duplicate records

In requirement we need Taransaction ID in output

It is not fetching any data

Actually that member have transaction is on that process dates

Can anyone help me from this

Find more posts tagged with

Accepted answers

All comments

Nikhila reddy

Query with out inculding Transaction Id:

select count(to_date(T155348.X_PROCESS_DATE)) as c1,

count(T155106.X_MEMBER_NUM) as c2,

count(T155348.X_PNR) as c3,

1 as c4,

T155106.X_MEMBER_NUM as c5,

T155348.X_PNR as c6,

to_date(T155348.X_PROCESS_DATE) as c7

from

W_LOY_TRANSACTION_ID_D T155348 /* Dim_W_LOY_TRANSACTION_ID_D */ ,

W_LOY_MEMBER_D T155106 /* Dim_W_LOY_MEMBER_D */ ,

W_PRODUCT_D T67704 /* Dim_W_PRODUCT_D */ ,

W_LOY_ACTIVITY_F T154500 /* Fact_W_LOY_ACTIVITY_F */

where ( T67704.PROD_NAME = 'WSD Purchase'

and T67704.ROW_WID = T154500.PRODUCT_WID

and T154500.TXN_NUM_WID = T155348.ROW_WID

and T154500.MEMBER_WID = T155106.ROW_WID

and T155348.TXN_STATUS_CODE = 'Processed' )

group by T155106.X_MEMBER_NUM, T155348.X_PNR, to_date(T155348.X_PROCESS_DATE)),

SAWITH1 AS (select distinct 0 as c1,

D1.c5 as c2,

D1.c6 as c3,

D1.c7 as c4,

D1.c4 as c5

from

SAWITH0 D1

where ( 1 < D1.c1 and 1 < D1.c2 and 1 < D1.c3 ) )

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,

D1.c2 as c2,

D1.c3 as c3,

D1.c4 as c4

from

SAWITH1 D1

order by c1, c2, c3, c4 ) D1 where rownum <= 5000001

Query after inclusing Transaction Id

select count(to_date(T155348.X_PROCESS_DATE)) as c1,

count(T155106.X_MEMBER_NUM) as c2,

count(T155348.X_PNR) as c3,

1 as c4,

T155106.X_MEMBER_NUM as c5,

T155348.X_PNR as c6,

to_date(T155348.X_PROCESS_DATE) as c7,

T155348.X_TRANSACTION_ID as c8

from

W_LOY_TRANSACTION_ID_D T155348 /* Dim_W_LOY_TRANSACTION_ID_D */ ,

W_LOY_MEMBER_D T155106 /* Dim_W_LOY_MEMBER_D */ ,

W_PRODUCT_D T67704 /* Dim_W_PRODUCT_D */ ,

W_LOY_ACTIVITY_F T154500 /* Fact_W_LOY_ACTIVITY_F */

where ( T67704.PROD_NAME = 'WSD Purchase'

and T67704.ROW_WID = T154500.PRODUCT_WID

and T154500.TXN_NUM_WID = T155348.ROW_WID

and T154500.MEMBER_WID = T155106.ROW_WID

and T155348.TXN_STATUS_CODE = 'Processed' )

group by T155106.X_MEMBER_NUM, T155348.X_TRANSACTION_ID, T155348.X_PNR, to_date(T155348.X_PROCESS_DATE)),

SAWITH1 AS (select distinct 0 as c1,

D1.c5 as c2,

D1.c6 as c3,

D1.c7 as c4,

D1.c8 as c5,

D1.c4 as c6

from

SAWITH0 D1

where ( 1 < D1.c1 and 1 < D1.c2 and 1 < D1.c3 ) )

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select D1.c1 as c1,

D1.c2 as c2,

D1.c3 as c3,

D1.c4 as c4,

D1.c5 as c5

from

SAWITH1 D1

order by c1, c2, c3, c4, c5 ) D1 where rownum <= 5000001

asim cholas

what is the physical query result in DB?

Nikhila reddy

As this queries are generated From Analytics this are giving the same results which we are getting in Analytics,

Query with out inculding Transaction Id: is giving dublicate records

Query after inclusing Transaction Id: is ging no data

asim cholas

one of the table may be causing issue. Try removing columns other than transaction ID and check which table join is causing this. It is something with the joins and tables only. when it is getting grouped by transaction ID

Nikhila reddy

Yes Asim

I tried checking that but i didnt find any difference between those 2 Queries

this issue am facing when i pulling Transaction_id or Transaction_num, for rest of columns from same table are giving results

asim cholas

did you try removing the count filter (the third filter and see if you get the records). Why there is aggregation on dimension columns. It should be for fact columns.

Nikhila reddy

It is to fetch the duplicate Records Where member ID, processed date and PNR # are the same,

So that filter will give Dublicate records

asim cholas

It seems like a wrong model. Check if you have 1 -> N relationship between tables. Provide create statement and insert statement (for few records), so that we can test here.