Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Duplicate Transaction Filter Issue

Received Response
31
Views
8
Comments
Nikhila reddy
Nikhila reddy Rank 4 - Community Specialist
in Oracle Business Intelligence Applications

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

pastedImage_0.png

pastedImage_5.png

In requirement we need Taransaction ID in output

pastedImage_2.png

It is not fetching any data

pastedImage_3.png

Actually that member have transaction is on that process dates

pastedImage_4.png

Can anyone help me from this

Answers

  • Nikhila reddy
    Nikhila reddy Rank 4 - Community Specialist

    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
    asim cholas Rank 6 - Analytics Lead

    what is the physical query result in DB?

  • Nikhila reddy
    Nikhila reddy Rank 4 - Community Specialist

    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
    asim cholas Rank 6 - Analytics Lead

    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
    Nikhila reddy Rank 4 - Community Specialist

    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
    asim cholas Rank 6 - Analytics Lead

    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
    Nikhila reddy Rank 4 - Community Specialist

    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
    asim cholas Rank 6 - Analytics Lead

    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.