2 Replies Latest reply: Jan 20, 2016 1:50 PM by ASma Erum RSS

    Graph displays a blank category with Sales cost

    ASma Erum

      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

          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
              ASma Erum

              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.