topic Re: Graph displays a blank category with Sales cost in App Development

Graph displays a blank category with Sales cost

Wed, 20 Jan 2016 18:14:23 GMT

I am attempting to create a report which pulls data from MSSQL database.

There are currently two issues I am experiencing with this report:

1. It does not show Inventory items with no sales and no cost.

2. There is a blank category that appears with a sales value. However it is disabled.

Here is what my current data model looks like:

Inventory:

Load *;

SQL SELECT

RTRIM(m.masterid) masterid,

masterno,

master as ICodeDescription,

availfrom,

availfor,

categoryid

FROM rentalworks.dbo.master m

WHERE availfor='S'

and availfrom = 'W'

and inactive<>'T';

Category:

Load *;

SQL SELECT

category,

categoryid

FROM Rentalworks.dbo.category c

WHERE inactive<>'T';

INNER JOIN (Category)

Load categoryid

Resident Inventory;

Warehouse:

Load *;

SQL SELECT

warehouse,

warehouseid

FROM rentalworks.dbo.warehouse

where inactive<>'T';

/* ----------------- Load, Select and Join Sales and Purchases ----------*/

SalesAndPurchases:

Load

warehouse,

masterid,

//masterno,

//ICodeDescription,

Date(TransactionDate, 'YYYY-MM-DD') as TransactionDate,

TransactionType,

orderno,

UnitPurchasePrice,

PurchaseQty,

ExtendedCostPrice;

SQL SELECT

sc.warehouse,

RTRIM(sc.InventoryKey) as masterid,

//RTRIM(sc.ICode) as masterno,

//RTRIM(ICodeDescription) as ICodeDescription,

sc.TransactionDate,

sc.TransactionType,

REVERSE(SUBSTRING(REVERSE(RTRIM(sc.ChangeDescription)), 1,

CHARINDEX(' ', REVERSE(RTRIM(sc.ChangeDescription))) - 1)) as orderno,

case when (sc.TransactionType='PURCHASE') then UnitPriceAddition

when (sc.TransactionType ='VENDOR RETURN') then UnitPriceSubtraction

end as UnitPurchasePrice,

case when (sc.TransactionType='PURCHASE') then AdditionQty

when (sc.TransactionType='VENDOR RETURN') then SubtractionQty

end as PurchaseQty,

case when (sc.TransactionType='PURCHASE') then ExtendedAdditionPrice

when (sc.TransactionType='VENDOR RETURN') then ExtendedSubtractionPrice

end as ExtendedCostPrice

FROM PSRentalworksDW.dbo.SalesInventoryChange sc

WHERE (sc.TransactionType ='PURCHASE' OR sc.TransactionType='VENDOR RETURN')

AND sc.TransactionDate >= '2013-01-01';

Concatenate (SalesAndPurchases)

Load

warehouse,

masterid,

//masterno,

//ICodeDescription,

Date(TransactionDate, 'YYYY-MM-DD') as TransactionDate,

TransactionType,

orderno,

UnitSalePrice,

SalesQty,

ExtendedSalesPrice;

SQL SELECT

es.warehouse,

RTRIM(es.masterid) masterid,

//es.masterno,

//es.description as ICodeDescription,

es.transdate as TransactionDate,

es.transtype as TransactionType,

es.orderno,

es.price as UnitSalePrice,

es.qtyordered as SalesQty,

es.priceextended as ExtendedSalesPrice

FROM RWReports.dbo.ExpendableSales es;

INNER JOIN (SalesAndPurchases)

Load warehouse

Resident Warehouse;

LEFT JOIN (SalesAndPurchases)

Load masterid

Resident Inventory;

I am not sure why either of that is happening as all items belong to a category.

Re: Graph displays a blank category with Sales cost

Oleg_Troyansky — Wed, 20 Jan 2016 18:35:17 GMT

Hi Asma,

generally speaking, both problems are happening because of your INNER JOINs and LEFT JOINs... By using INNER and LEFT, you are limiting your field values to only those that have either Inventory, or Sales, or Both.

For example:

INNER JOIN (Category)

Load categoryid

Resident Inventory;

this statement limits Categories only to those that have Inventory. This is the reason for your problem #2 - you have Sales for certain Categories that don't exist. They don't exist (most likely) because they don't have Inventory and therefore got excluded.

In a similar way, your inventory items that don't have sales must be excluded by an INNER or a LEFT JOIN (I didn't scan your code to find the specific load, but it is very likely the case.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Re: Graph displays a blank category with Sales cost

Wed, 20 Jan 2016 18:41:21 GMT

That is simply getting only Inventory Items that belongs to Category. Every Inventory Item belong to a category. You cannot have Inventory which does not belong to a Category. Also even with that condition removed, I see Sales for Categories that do not exist. I am thinking there must be something wrong with the concatenation statement for SalesAndPurchases. But I cannot seem to figure out what the problem is exactly.

Also for the current year 2016. The graph is only displaying categories that have Sales OR Purchases. However, I want it to display all categories regardless of whether it has Sales or Purchases.