topic Re: Double Qty Persists in QlikView

Double Qty Persists

oseimuduame — Wed, 25 Nov 2020 16:16:04 GMT

Good Afternoon,

i had an issue some months Back and i got a response.

The issue is that i was experiencing Double Qty some times back and i used DISTINCT to correct this and it worked for sometime only for me to realize now that there are still some occurences of the Duplicate item qty.

Please any idea on how to resolve this.

//inventory qty

SQL SELECT DISTINCT

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",

q.MIN_QTY as "MIN_QTY",

q.MAX_QTY as "MAX_QTY",

q.TRANSFER_IN_QTY as "TRANS_IN",

q.TRANSFER_OUT_QTY as "TRANS_OUT",

(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 q.STORE_NO < 250 AND q.SBS_NO in $(subsidiaries);

Thank You.

Re: Double Qty Persists

sasiparupudi1 — Tue, 13 Mar 2018 15:27:07 GMT

Where do you see the double qty in which field? this looks like asql issue rather than a qlikview issue

Re: Double Qty Persists

fvelascog72 — Tue, 13 Mar 2018 15:37:16 GMT

Hi,

Maybe duplicated values in INVN_SBS?

Saludos

Re: Double Qty Persists

shiveshsingh — Tue, 13 Mar 2018 16:48:12 GMT

Is your data in INVN_SBS_QTY and INVN_SBS is unique?

Are you facing issue after join?

Re: Double Qty Persists

oseimuduame — Tue, 13 Mar 2018 17:00:42 GMT

I am seeing the Double Qty in the INVN_SBS_QTY which means that for most of the Items the QTY is double e.g 7 will become 14. This was why i used distinct but still experiencing the double qty

Re: Double Qty Persists

oseimuduame — Tue, 13 Mar 2018 17:01:59 GMT

The DB cannot hold Double Values i have double Checked. I picked on item and i checked the QTY on the Software and Compare to Qlikview it still Duplicating.

Any help please.

Re: Double Qty Persists

oseimuduame — Tue, 13 Mar 2018 17:02:41 GMT

Yes the Data are UNIQUE..

And i think the Duplicate comes after Joining.

Any help please

Re: Double Qty Persists

shiveshsingh — Tue, 13 Mar 2018 17:05:24 GMT

Then there can be an issue in joining. May be one value is getting joined to 2 values in another table.

Need to debug!

Can you share your app?

Re: Double Qty Persists

Nagaraju_KCS — Tue, 13 Mar 2018 17:05:36 GMT

I think you have one to many relation.

may be data repeating twice.

Table1:

ID	Amount
100	200
200	300
300	400

Table2:

ID	Category
100	A
100	B
200	C
300	R
300	R

If we use inner join data repeats, because we have one to many...

Instead of Inner Join use Inner Keep. (Convert into Qlikview)

I think your query is simple, better to convert into Qlikview, and use left Keep.

that would work.

Thanks....!

Re: Double Qty Persists

oseimuduame — Tue, 13 Mar 2018 17:12:16 GMT

I tried this script but keep saying Missing Keyword...

//inventory 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",

q.MIN_QTY as "MIN_QTY",

q.MAX_QTY as "MAX_QTY",

q.TRANSFER_IN_QTY as "TRANS_IN",

q.TRANSFER_OUT_QTY as "TRANS_OUT",

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

FROM INVN_SBS_QTY q

LEFT KEEP INVN_SBS n ON n.ITEM_SID = q.ITEM_SID

WHERE q.STORE_NO < 250 AND q.SBS_NO in $(subsidiaries);

Am i missing something here

Re: Double Qty Persists

Nagaraju_KCS — Tue, 13 Mar 2018 17:28:54 GMT

No, This is SQL script.

You have to convert Qlikview level.

See below....

INVN_SBS_QTY:

SQL SELECT

TO_CHAR(q.ITEM_SID) AS "ITEM_SID",

SBS_NO AS "SBS_NO",

STORE_NO AS "STORE_NO",

QTY as "OH_QTY",

MIN_QTY as "MIN_QTY",

MAX_QTY as "MAX_QTY",

TRANSFER_IN_QTY as "TRANS_IN",

TRANSFER_OUT_QTY as "TRANS_OUT",

QTY AS Qty // Quantity in INVN_SBS_QTY

FROM INVN_SBS_QTY

WHERE STORE_NO < 250 AND SBS_NO in $(subsidiaries);

Store INVN_SBS_QTY into path/INVN_SBS_QTY .qvd(qvd);

INVN_SBS:

SQL SELECT

ITEM_SID

COST AS COST // Cost in INVN_SBS

FROM INVN_SBS;

Store INVN_SBS into path/INVN_SBS.qvd(qvd);

Create like 2 separate QVD's INVN_SBS_QTY and INVN_SBS;

then use like below...

Load

ITEM_SID,

SBS_NO,

STORE_NO,

OH_QTY,

MIN_QTY,

MAX_QTY,

TRANS_IN,

TRANS_OUT,

QTY

From INVN_SBS_QTY ;

left Keep

Load

ITEM_SID

COST

FROM INVN_SBS;

This should work...:)