6 Replies Latest reply: Jan 3, 2016 12:39 PM by ASma Erum RSS

    Multiple measures on Chart using start and end date and master calendar

    ASma Erum

      I am fairly new to qlik sense and currently working on a chart to display different measures (rented qty, owned qty, repaired qty) on a line chart.

       

      I was able to add Owned qty and repaired qty and use the master calendar for the date. However, I have loaded another table from our databases which has the rentstartdate, rentenddate along with the item. How would I join the date parameters with the existing master calendar?

       

      Here is what the current script looks like:

       

      InventoryLoad:

      Load *;

      SQL SELECT

      IDate,

      ItemKey,

      Ownedqty,

      Repairedqty

      FROM inventory

      order by IDate asc;

       

      LoadRented:

      Load *;

      SQL SELECT

      ItemKey,

      rentedqty,

      rentstart,

      rentend

      FROM orderrents;

       

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

      'Q' & Ceil (rowno()/3) as Quarter 

      AUTOGENERATE (12); 

           

      Temp: 

      Load 

      min(IDate) as minDate, 

      max(IDate) as maxDate 

      Resident LoadInventory; 

           

      Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

      DROP Table Temp; 

           

      TempCalendar: 

      LOAD 

      $(varMinDate) + Iterno()-1 As Num, 

      Date($(varMinDate) + IterNo() - 1) as TempDate 

      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

           

      MasterCalendar: 

      Load 

        TempDate AS IDate, 

        week(TempDate) As Week, 

        Year(TempDate) As Year, 

        Month(TempDate) As Month, 

        Day(TempDate) As Day, 

        ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

        Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

        WeekDay(TempDate) as WeekDay 

      Resident TempCalendar 

      Order By TempDate ASC; 

      Drop Table TempCalendar; 

       

      How would I also map the rentstart and rentend to the mastercalendar?

      Would that be done via simple join?

        • Re: Multiple measures on Chart using start and end date and master calendar
          Gysbert Wassenaar

          You can use the IntervalMatch function to match the intervals defined by rentstart and rentend with the IDate values:

           

          RentIntervals:

          IntervalMatch(IDate,ItemKey)

          LOAD rentstart, rentent,ItemKey

          Resident LoadRented;

            • Re: Multiple measures on Chart using start and end date and master calendar
              ASma Erum

              Thanks. But this displays the following error:

              $Syn 1 = ItemKey+IDate

               

              Circular reference:

               

               

              One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. Loop(s) will automatically be cut by setting one or more tables as loosely coupled. Use Loosen Table script statement to explicitly declare loosely coupled tables.
              This would not exist across 3 tables: LoadInventory, LoadRented, and RentIntervals.
              Should I drop LoadRented?

               

                • Re: Multiple measures on Chart using start and end date and master calendar
                  Gysbert Wassenaar

                  Yes, that's a good solution. But first add the fields from LoadRented to RentIntervals:

                   

                  RentIntervals:

                  IntervalMatch(IDate,ItemKey)

                  LOAD rentstart, rentent,ItemKey

                  Resident LoadRented;

                   

                  Left Join (RentIntervals)

                  Load * Resident LoadRented;

                   

                  Drop Table LoadRented;

                   

                  If you don't care about the rentstart and rentend fields you could also join RentIntervals to InventoryLoad and then drop RentIntervals and the rentstart and rentend fields.

                    • Re: Multiple measures on Chart using start and end date and master calendar
                      ASma Erum

                      This seemed to work partially.

                      I am still getting the synthetic keys:

                      Lines fetched: 1

                      $Syn 1 = warehouse+InventoryKey+IDate
                      I now have the RentalIntervals table and Load Inventory which is causing this issue. Is there a way that I can keep them as distinct tables but get rid of the synthetic table error?

                       

                        • Re: Multiple measures on Chart using start and end date and master calendar
                          Gysbert Wassenaar

                          Oh, you have a warehouse too. Your first post didn't mention it. Are there any more parts of your data model that share keys with the parts you posted in your original post?

                            • Re: Multiple measures on Chart using start and end date and master calendar
                              ASma Erum

                              I have made some changes and this is what the current data model looks like:

                               

                              /* Gather Warehouses */

                              Warehouses:

                              Load *;

                              SQL SELECT

                              RTRIM(warehouse) warehouse,

                              warehouseid

                               

                               

                              FROM Rent.dbo.warehouse

                              where inactive<>'T';

                               

                              /* Gather Active Icodes */

                              Inventory:

                              Load *;

                              SQL SELECT

                              InventoryKey,

                              ICode as masterno,

                              Description,

                              Department,

                              Category,

                              SubCategory

                              FROM RentDW.dbo.Inventory

                              Where InactiveFlag<>'T'

                              AND AvailFor='RENT'

                              AND Len(ICode)=7;

                               

                               

                              /*

                              DailyInventory:

                              Load *;

                              SQL SELECT

                              warehouse,

                              ICode as masterno,

                              TransactionDate,

                              TransactionType as Type,

                              ChangeDescription,

                              AdditionQty as AddedQty,

                              SubtractionQty as SubtractedQty,

                              ExtendedAdditionPrice as AddedValue,

                              ExtendedSubtractionPrice as SubtractedValue

                               

                               

                              FROM RentDW.dbo.InventoryChange; */

                               

                               

                               

                               

                              LoadDailyOwnedRepairInventory:

                              Load *;

                              SQL SELECT

                              I.InventoryKey,

                              d.Date as IDate,

                              d.Ownedqty,

                              d.RepairQty,

                              RTRIM(warehouse) warehouse

                               

                               

                              From RentDW.dbo.DailyInventoryStatus d, Inventory I, Time t

                              WHERE d.Date = t.Date

                              and I.inventoryKey = d.InventoryKey

                              Order By IDate ASC ;

                               

                               

                              INNER JOIN

                              Load warehouse Resident Warehouses;

                               

                               

                               

                               

                              MinMaxDates:

                              Load

                              Min(IDate) as MinDate,

                              Max(IDate) as MaxDate

                              Resident LoadDailyOwnedRepairInventory; 

                               

                               

                              Let varMinDate = Num(Peek('MinDate', 0, 'MinMaxDates')); 

                              Let varMaxDate = Num(Peek('MaxDate', 0, 'MinMaxDates'));

                               

                               

                              LoadRented:

                              Load *;

                              SQL SELECT

                              ISNULL(ISNULL(outreceivedatetime, estrentfrom),billperiodstart) as outdatetime,

                              ISNULL(ISNULL(inreturndatetime, estrentto),billperiodend) indatetime,

                              masterid as InventoryKey,

                              qty as RentedQty,

                              d.warehouse as warehouse

                               

                               

                              FROM Rent.dbo.Ordertran o, Rent.dbo.masteritem m , Rent.dbo.orderview d

                              WHERE m.masteritemid = o.masteritemid and m.orderid = o.orderid and d.orderid = o.orderid

                              AND (inreturndatetime IS NULL OR inreturndatetime > $(varMinDate))

                              AND outreceivedatetime < $(varMaxDate)

                              AND d.dealid <> ''

                              AND o.rentalitemid ! = ''

                              AND d.status IN ('ACTIVE', 'CLOSED', 'COMPLETE', 'CONFIRMED');

                               

                               

                              INNER JOIN

                              Load warehouse Resident Warehouses;

                               

                               

                               

                               

                              RentIntervals:

                              IntervalMatch(IDate,InventoryKey)

                              LOAD outdatetime, indatetime,InventoryKey

                              Resident LoadRented;

                               

                              Left Join (RentIntervals)

                              Load * Resident LoadRented;

                               

                              Drop Table LoadRented;

                              Drop Table MinMaxDates;

                               

                              I am unsure how I can concatenate the table that provides the daily inventory with the table that provides the rented qty. Is it possible that I can keep them as separate tables and still get rid of the synthetic keys. Additionally, there would be additional data that would be added to this: i.e. 2 additional datasets, one for subrentqty, invoicedqty similar to LoadRented.