-
Re: Duplicate Data
Felip Drechsler Dec 7, 2017 10:43 AM (in response to Omotayo Olokede)Hi Omotayo,
Are you doing some sort of concatenate or join statement when retrivieng the data?
That might be causing the double entry data you see.
-
Re: Duplicate Data
Omotayo Olokede Dec 7, 2017 10:56 AM (in response to Felip Drechsler )Hi Felip,
I have some few LEFT JOIN, RIGHT JOIN and INNER JOIN, this was due to JOINING table together to get accurate DATA.
What do you suggest i can do to correct this?
-
Re: Duplicate Data
Felip Drechsler Dec 7, 2017 11:01 AM (in response to Omotayo Olokede)You'll have to see which combinations of data (left and right joins) are giving you the duplicated data.
What kind of combinations gives more than one line for your data that these joins are dupplicating the data.
It's more an investigative data search than something we can actually help without some sample data or QVW to work on.
Can you share some screenshots or app with example data?
-
-
Re: Duplicate Data
Felip Drechsler Dec 7, 2017 11:43 AM (in response to Omotayo Olokede)I'm assuming by the join in the sql statement that both the tables have more than one entry for each ITEM_SID.
Something like:
INVN_SBS table:
ITEM_SID, COST,MATERIAL
1,1,1
1,3,2
2,10
3,50
and the INVN_SBS_QTY might look something like this:
ITEM_SID,SBS_NO,STORE_NO,QTY
1,1,1,100
1,2,1,200
2,1,1,13
2,1,2,15
3,3,3,50
Which gives duplicate rows as below:
Since you're only joining by the ITEM_SID the combination of ITEM_SID on the INVN_SBS with the join by INVN_SBS_QTY table can generate such cases.
One option is to identify if adding another field (in my assumption case, the MATERIAL field) that differentiates the rows of both tables.
Can you share some rows as to how your data is duplicating and some related table data?
-
-
Re: Duplicate Data
Felip Drechsler Dec 7, 2017 12:10 PM (in response to Omotayo Olokede)It's definately a duplication issue.
Can you put all the fields of the original tables and take a screenshot or paste it into an Excel spreadsheet after the join?-
-
Re: Duplicate Data
Felip Drechsler Dec 7, 2017 12:55 PM (in response to Omotayo Olokede)It would be necessary to get all field for both tables, INVN_SBS and INVN_SBS_QTY.
Can you get all fields from both tables for the first ITEM_SID that appears on your screenshot please?
-
Re: Duplicate Data
Omotayo Olokede Dec 15, 2017 7:39 AM (in response to Felip Drechsler )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...
-
Re: Duplicate Data
Felip Drechsler Dec 15, 2017 8:20 AM (in response to Omotayo Olokede)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.
-
Re: Duplicate Data
Omotayo Olokede Dec 15, 2017 12:35 PM (in response to Felip Drechsler )I am having difficulty attaching an excel sheet.
Any help...
-
Re: Duplicate Data
Omotayo Olokede Dec 15, 2017 12:37 PM (in response to Omotayo Olokede)can i have your email
-
Re: Duplicate Data
Felip Drechsler Dec 15, 2017 1:58 PM (in response to Omotayo Olokede)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;
-
Re: Duplicate Data
Omotayo Olokede Dec 15, 2017 5:23 PM (in response to Felip Drechsler )Thank You Felip.
I will try just this and see how it goes
-
Re: Duplicate Data
Omotayo Olokede Dec 18, 2017 10:29 AM (in response to Felip Drechsler )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?
-
Re: Duplicate Data
Robert Hutchings Dec 18, 2017 10:38 AM (in response to Omotayo Olokede)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.
-
Re: Duplicate Data
Omotayo Olokede Dec 18, 2017 11:18 AM (in response to Robert Hutchings)Never thought of that but sure will do
-
-
Re: Duplicate Data
Felip Drechsler Dec 18, 2017 10:41 AM (in response to Omotayo Olokede)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.
-
Re: Duplicate Data
Omotayo Olokede Dec 18, 2017 11:17 AM (in response to Felip Drechsler )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
-
Re: Duplicate Data
Felip Drechsler Dec 18, 2017 11:22 AM (in response to Omotayo Olokede)No problem Omotayo,
Hope I could direct you in some way to the solution .
Felipe.
-
Re: Duplicate Data
Omotayo Olokede Dec 18, 2017 11:30 AM (in response to Felip Drechsler )You opened my eyes to some import SQL Statements and Redundancy when running a script.
I really appreciate.
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-