Duplicate Data

[oseimuduame]

Good Morning,

I have a small challenge here, i am fetching data from an Oracle Database into Qlikview but then i discovered that the data i am getting is twice what i have in my Oracle Database.

Please any help please as this just scattered my whole report.

Await a speedy response.

Thank You in advance

[oseimuduame]

Thank You for your response. i had issues with my system but its fixed now.

So below is the list of Fields for the two tables..

//INVN_SBS

SQL SELECT

  TO_CHAR(n.ITEM_SID) AS "ITEM_SID",

  n.ITEM_NO AS "ITEM_NO",

  n.SBS_NO AS "SBS_NO",

    n.ACTIVE AS "ITEM_ACTIVE",

  n.VEND_CODE AS "VEND_CODE",

  n.DCS_CODE AS "DCS_CODE",

  n.DESCRIPTION1 AS "ITEM_DESCRIPTION1",

  n.DESCRIPTION2 AS "ITEM_DESCRIPTION2",

  n.DESCRIPTION3 AS "ITEM_DESCRIPTION3",

  n.DESCRIPTION4 AS "ITEM_DESCRIPTION4",

  n.ATTR AS "ITEM_ATTR",

  n.SIZ AS "ITEM_SIZ",

  SUBSTR(d.DCS_CODE,1,3) AS "D_CODE",

  SUBSTR(d.DCS_CODE,4,3) AS "C_CODE",

  SUBSTR(d.DCS_CODE,7,3) AS "S_CODE",

  d.D_NAME AS "D_NAME",

  d.C_NAME AS "C_NAME",

  d.S_NAME AS "S_NAME",

  u.UPC AS "UPC",

  d.D_LONG_NAME AS "D_LONG_NAME",

  d.C_LONG_NAME AS "C_LONG_NAME",

  d.S_LONG_NAME AS "S_LONG_NAME",

  d.ACTIVE AS "DCS_ACTIVE",

  n.COST AS "ITEM_COST",

  TO_CHAR(n.FST_RCVD_DATE, 'DD-MM-YYYY') AS "FIRST_RCVD_DATE",

  TO_CHAR(n.FST_RCVD_DATE, 'YYYY') AS "FIRST_RCVD_YEAR",

  TO_CHAR(n.FST_RCVD_DATE, 'MM') AS "FIRST_RCVD_MONTH",

  TO_CHAR(n.FST_RCVD_DATE, 'DD') AS "FIRST_RCVD_DAY",

  TO_CHAR(n.LST_RCVD_DATE, 'DD-MM-YYYY') AS "LAST_RCVD_DATE",

  TO_CHAR(n.LST_RCVD_DATE, 'YYYY') AS "LAST_RCVD_YEAR",

  TO_CHAR(n.LST_RCVD_DATE, 'MM') AS "LAST_RCVD_MONTH",

  TO_CHAR(n.LST_RCVD_DATE, 'DD') AS "LAST_RCVD_DAY"

FROM INVN_SBS n

LEFT JOIN INVENTORY u ON n.ITEM_SID = u.ITEM_SID

LEFT JOIN INVC_ITEM i ON i.ITEM_SID = n.ITEM_SID

LEFT JOIN VENDOR v ON v.VEND_CODE = n.VEND_CODE AND v.SBS_NO = n.SBS_NO

LEFT JOIN DCS d ON d.DCS_CODE = n.DCS_CODE AND d.SBS_NO = n.SBS_NO

WHERE n.SBS_NO in $(subsidiaries) and n.ACTIVE = 1;

//INVN_SBS_QTY

SQL SELECT

  TO_CHAR(q.ITEM_SID) AS "ITEM_SID",

  q.SBS_NO AS "SBS_NO",

  q.STORE_NO AS "STORE_NO",

  ((q.QTY)) as "OH_QTY",

  (n.COST * q.QTY) AS "EXT_COST"

FROM INVN_SBS_QTY q

INNER JOIN INVN_SBS n ON n.ITEM_SID = q.ITEM_SID

WHERE n.ACTIVE = 1 AND q.STORE_NO < 250 AND q.SBS_NO in $(subsidiaries);

Any Help Please...

[felipedl]

Hi Omotayo,

I forgot to mention, with this select statmentes, can you please put some of the data into a file (Excel for example) so that it's possible to check whats going on?

Simply by the SQL statements it's difficult to debug the issue without actual data.

Please run  the above statement for a few connected items (on both tables) so that the join might get tested in Qlik.

Thanks,

Felipe.

[oseimuduame]

I am having difficulty attaching an excel sheet.

Any help...

[oseimuduame]

can i have your email

[felipedl]

Hi Omotayo,

I've seen the files you posted on another thread, and to me, it seems your loading the data twice, once from the Quantity table, and the other from the Items table, do you need both sql statements?

Can you try the code below to check if it's resolved?

// Gets the Item quantites from the table

ITEM_QTY:

SQL Select

TO_CHAR(q.ITEM_SID) as "ITEM_SID",

q.SBS_NO as "SBS_NO",

q.STORE_NO as "STORE_NO",

((q.QTY)) as "OH_QTY",

(n.COST * q.QTY) as "EXT_COST"

FROM INVN_SBS_QTY q

INNER JOIN INVN_SBS n ON n.ITEM_SID = q.ITEM_SID

WHERE n.ACTIVE = 1 AND q.STORE_NO < 250 AND q.SBS_NO in $(subsidiaries);

// Gets the Item from the table

ITEMS:

SQL SELECT

TO_CHAR(q.ITEM_SID) as "ITEM_SID",

n.SBS_NO as "SBS_NO",

n.DESCRIPTION1 as "ITEM_DESCRIPTION1",

n.DESCRIPTION2 as "ITEM_DESCRIPTION2",

n.DESCRIPTION3 as "ITEM_DESCRIPTION3",

n.DESCRIPTION4 as "ITEM_DESCRIPTION4",

n.ATTR as "ITEM_ATTR"

FROM INVN_SBS n

LEFT JOIN INVERNTORY u ON n.ITEM_SID = u.ITEM_SID

LEFT JOIN INVC_ITEM i ON i.ITEM_SID = n.ITEM_SID

LEFT JOIN VENDOR v ON v.VEND_CODE = n.VEND_CODE and v.SBS_NO = n.SBS_NO

LEFT JOIN DCS d ON d.DCS_CODE = n.DCS_CODE AND d.SBS_NO = n.SBS_NO

Where n.SBS_NO in $(subsidiaries) and n.ACTIVE = 1;

// joins both tables to get the descriptions, based on common fields

left join (ITEM_QTY)

load

ITEM_SID,

SBS_NO,

ITEM_DESCRIPTION1,

ITEM_DESCRIPTION2,

ITEM_DESCRIPTION3,

ITEM_DESCRIPTION4,

ITEM_ATTR

resident ITEMS;

drop table ITEMS;

[oseimuduame]

Thank You Felip.

I will try just this and see how it goes

[oseimuduame]

Dear Felip,

After trying this script it now gives me three time of what i am suppose to get.

where i have 2 i now have 6..

Any idea what is going on?

[robert99]

Hi

Have you tried (left keep etc) rather than join. In this way the duplication issue should be easier to locate.

edit. I try to avoid joining tables. I prefer either using keep. Or use mapping. In this way the duplication issue if it occurs is not so bad.

[felipedl]

Are you just loading the code I've described above?

Since the join is producing 6 instead of two, there are tree entries in the ITEMS table with the combination

ITEM_SID, SBS_NO

Meaning that there are other fields in the ITEMS table that differentiate each row, and the combination ITEM_SID and SBS_NO is not unique.

Could be for example, that for an ITEM_SID and SBS_NO there are different dates (possibly same item but produced on different years or some sort of logic like that). and therefore the join is multiplying the results.

[oseimuduame]

What i will do is to run the SQL SCRIPT one after the other and no where the problem is.

Thank You for you time and Knowledge. I do hope we will work together in stuffs like this