topic Re: Date Fields in QlikView

Date Fields

Fri, 07 Feb 2014 00:13:32 GMT

Hi Guys,

Quite new to Qlikview but finding it quite simple to learn and understand with some help of videos etc. However, I'm a little stuck with this next peice.

I love the fact that you just LOAD your fields in and ALIAS them to join. However, how do I go about it for Dates? I'm using the below for my dates on one able :

Table1:

Date(DayStart(RaisedDTStamp)) AS Date,

Hour(RaisedDTStamp) AS Hour,

Time(RaisedDTStamp) AS Time,

WeekDay(RaisedDTStamp) AS Weekday,

Month(RaisedDTStamp) AS Month,

Year(RaisedDTStamp) AS Year,

Table2:

,Date(DayStart(TM1OffsetDateTime)) AS Date

and its fine, However when using the same Alias "AS Date" on the second tables Date field I get a message saying:

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.

Is this a problem? I then have some dotted line on the table viewer but not clear whether they highlight a problem or something Qlikview is handling?

Anyone help out? Let me know if you need more information....

Thanks in advance

Fodzilla

Re: Date Fields

Anonymous — Fri, 07 Feb 2014 03:28:44 GMT

Hey Fodzilla,

You should fix all Loops and Synthetic Keys in your data model. They can cause unexpected results with your calculations in your final application. Without seeing your data model I suspect you have a third table that also has the field name "Date" in it. This is causing the Loop. You can either Join, Concatenate, or rename the field to fix the issue but without seeing the data model I can't tell you what is the best solution.

Re: Date Fields

hic — Fri, 07 Feb 2014 08:22:38 GMT

A circular reference is certainly a problem and should be fixed, just as Michael says. Read more here: http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references

On the handling of dates, read more here:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

HIC

Re: Date Fields

Fri, 07 Feb 2014 10:58:27 GMT

I'm still a little lost with this, can anyone help me out?

PopupTable:

LOAD ClassifiedId,

EquipmentId AS EquipmentID,

Date(DayStart(RaisedDTStamp)) AS Date,

Hour(RaisedDTStamp) AS Hour,

Time(RaisedDTStamp) AS Time,

WeekDay(RaisedDTStamp) AS Weekday,

Month(RaisedDTStamp) AS Month,

Year(RaisedDTStamp) AS Year,

EquipmentName,

Specialist,

Classification1,

Classification2,

Classification3,

Classification1 & Classification2,

Abandoned,

ChecksRequestId,

IF(ChecksRequestId > 0,'Checks Requests',' ') AS ChecksRequest;

SELECT [ClassifiedId]

,[Id]

,[RaisedDTStamp]

,[OperatorId]

,[EquipmentId]

,[Source]

,[waterParkName]

,[OperatorName]

,[EquipmentName]

,[Specialist]

,[Classification1]

,[Classification2]

,[Classification3]

,[Abandoned]

,[ChecksRequestId]

FROM [NCP_Intercom].[dbo].[ICClassified]

IntercomCodesTable:

LOAD [Code] AS EquipmentID

,[SiteCode] AS SiteCode;

SELECT [Code]

,[SiteName]

,[Equipment]

,[SiteCode]

FROM [SisterStats2010].[dbo].[IntercomCodes];

iWebTable:

LOAD [waterparkCode] AS SiteCode

,[waterParkDesc] AS waterParkName

,[GeoCode]

,[GeoCode2];

SELECT [waterparkCode]

,[waterParkDesc]

,[GeoCode]

,[GeoCode2]

FROM [waterparkfinder].[dbo].[iWebwaterparks];

CustomerExitsTable:

LOAD [SiteCode] AS SiteCode

,Date(DayStart(CustomerOffsetDateTime)) AS Date

,[CustomerEntries]

,[CustomerExits]

,[UniqueReference];

SELECT [EquipmentType]

,[EquipmentSite]

,[SiteCode]

,[CustomerDate]

,[UnknownField1]

,[CustomerYear]

,[IntHour]

,[CustomerInterval]

,[UnknownField2]

,[UnknownField3]

,[CustomerEntries]

,[CustomerExits]

,[CustomerDateTime]

,[CustomerOffsetDateTime]

,[UniqueReference]

FROM [SisterStats2010].[dbo].[fg_tbl_Usage]

Re: Date Fields

hic — Fri, 07 Feb 2014 12:27:57 GMT

Looking at the script doesn't help us very much... Do a ctrl-T and post the data model or post a sample file (File - Reduce Data - keep possible values)

HIC

Re: Date Fields

Fri, 07 Feb 2014 12:53:02 GMT

Here is my data model, I am trying to it up so that the date in the tm1exitstable: and poptable: match without losing the sitecode joins.

Thanks

Re: Date Fields

hic — Fri, 07 Feb 2014 13:18:47 GMT

So, you have two dates: RaisedDTStamp and CustomerOffsetDateTime. These two dates are most likely not the same, and should therefore not be linked. Then you should have two Master Calendars. Read the links about dates that posted earlier on.

There is a trick you can use so you get one date that covers both. It is described in this comment: http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles#comment-12934

The idea is to link the most atomic ID (usually the ID in the table where you have most records) to a generic date with two different records; one for each date role.

HIC

Re: Date Fields

sunilkumarqv — Fri, 07 Feb 2014 13:46:17 GMT

Make use of resident . Its reduce data rebundency then youcan see.

Table1:

Date(DayStart(RaisedDTStamp)) AS Date,

Hour(RaisedDTStamp) AS Hour,

Time(RaisedDTStamp) AS Time,

WeekDay(RaisedDTStamp) AS Weekday,

Month(RaisedDTStamp) AS Month,

Year(RaisedDTStamp) AS Year,

Table2:

,Date(DayStart(TM1OffsetDateTime)) AS Date

Resident Table1;