topic Re: Multiple measures on Chart using start and end date and master calendar in App Development

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

Wed, 23 Dec 2015 17:02:55 GMT

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 — Wed, 23 Dec 2015 17:36:24 GMT

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

Wed, 23 Dec 2015 20:08:46 GMT

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 — Sat, 26 Dec 2015 17:30:54 GMT

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

Tue, 29 Dec 2015 07:16:37 GMT

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 — Tue, 29 Dec 2015 10:50:48 GMT

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

Sun, 03 Jan 2016 17:39:02 GMT

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.