6 Replies Latest reply: Apr 13, 2012 11:36 AM by Simon Goodman RSS

How to fix a date/data link problem and then restrict data?

Simon Goodman

I am having problems getting a standard calendar to link to the data table. The datefield in the data table was date&time so I tried to reformat it as Date(Datefield, .dd/mmm/yyyyy') as Date. Although the table viewer shows a connection between the data table and the calender table, it did not work.

 

I tried using floor(ModifiedOn) as Date and it partially worked for years 2010 and 2011 but not 2012. My questions are these;

 

Why did it not work?

 

What are the possible solutions?

 

Next is there a way to restrict the data load by date?

 

LET vDateMin = Num(MakeDate(2010,1,1));  
LET vDateMax = Floor(MonthEnd(Today()));  
LET vDateToday = Num(Today());  
TempCalendar:  
LOAD 
$(vDateMin) + RowNo() - 1 AS Num,  
Date($(vDateMin) + RowNo() - 1) AS TempDate  
AUTOGENERATE 1  
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);  

Calendar:
LOAD
    TempDate AS Date,  
 Year(TempDate) as Year,
 'Q' & ceil(month(TempDate)/3) as Quarter,
 date(monthstart(TempDate), 'YYYY-MM') as YearMonth,
 Month(TempDate) as Month,
 week(TempDate) & '-' & year(TempDate) as WeekYear,
 Week(TempDate) as Week,
 Weekday(TempDate) as WeekDay,
 Day(TempDate) as Day,
 Date(monthstart(TempDate), 'MM-YYYY') as MonthYear,
 inyeartodate(TempDate, $(vDateToday), 0) * -1 AS
   CurYTDFlag,
 inyeartodate(TempDate, $(vDateToday), -1) * -1 AS
   LastYTDFlag 
RESIDENT TempCalendar 
ORDER BY TempDate ASC;  
DROP TABLE TempCalendar;

//extract from the data table
floor(ModifiedOn) as Date,
  • How to fix a date/data link problem and then restrict data?
    swuehl

    Simon,

     

    even if you reformat a field like Date(Datefield, .dd/mmm/yyyyy') as Date.

    the internal representation is unchanged, so if your field encodes Time in decimal places, it will still do so.

     

    Most of your values won't match if you try to link a calendar with pure date values to a field with timestamp values (integer to float values).

     

    So you need to transform your field to pure date representation, not only reformat the string representation.

    Using floor() could do the job, or use daystart() to use one of the date/time functions.

     

    You said this worked only partially, and you had problems with current year. Could you describe this a bit more?

     

    It might be a good idea to look at the numerical representation of your calendar field and your fact table field to see what the mismatch causes.

     

    Regards,

    Stefan

    • Re: How to fix a date/data link problem and then restrict data?
      Simon Goodman

      Stefan

       

      Thanks I know understand about how the format did not change the internal

      set up. It makes perfect sense.

       

      I like your daystart suggestion, so in this case I think it would be:

      date(daystart(modifiedon)),

       

      I went back and spotted the villian. I originally started with the live db

      to get going and then switched to the test db. I thought the test db was a

      recent copy. It is not and does not have 2012 data. So floor works. A d'oh

      moment.

       

      So I am onto the next problem how to restrict data by date and another data

      value  which is a character field (ie text) in the load.

       

      Do you have any suggestions or helpful hints?

       

      On 10 Apr 2012 23:59, "swuehl" <qliktech@sgaur.hosted.jivesoftware.com

      • How to fix a date/data link problem and then restrict data?
        swuehl

        Have you looked into WHERE clause?

         

        You can look e.g. into the Incremental load sample in the QV cookbook (http://robwunderlich.com/downloads/)

        for an example of the usage and syntax.

        It shouldn't be a problem to use a where clause also with a character field.

         

        If you could post more details about your load, someone might help you with the syntax.

         

        Hope this helps,

        Stefan

        • Re: How to fix a date/data link problem and then restrict data?
          Simon Goodman

          Again thanks, I have tried the Where clause but not succesfully. Here

          are some examples:

           

          //************attempt to limit load for dates after 2011***********

          //where () > 2011;

          //where year() > 2011;

          //where num(date(ModifiedOn)) > 2011;

          //Where (ModifiedOn)>=2010 and (ModifiedOn)<=2011;

          //WHERE Date >= $(vDateMin) AND Date <= $(vDateMax);

           

          //************attempt to limit load by field with specific character

          description header*********

          //where (subject) = 'Paid*';

           

          Below is the load statement.

           

          //***Phone Call***

          PhoneCall:

          LOAD ActualDurationMinutes,

          //    ActualEnd,

          //    ActualStart,

          //    CreatedBy,

          //    CreatedByDsc,

          //    CreatedByName,

          //    CreatedByYomiName,

          //    CreatedOn,

          //    DeletionStateCode,

              Description,

          //    IsBilled,

          //    IsWorkflowCreated,

          //    "jsa_notgoingaheadcompetitoridDsc",

          //    "jsa_notgoingaheadcompetitoridName",

          //    "jsa_notgoingaheadcompetitoridYomiName",

          //    "jsa_smsmessagetemplateidDsc",

          //    "jsa_smsmessagetemplateidName",

          //    ModifiedBy,

          //    ModifiedByDsc,

          //    ModifiedByName,

          //    ModifiedByYomiName,

          //    Date(ModifiedOn, 'DD/MMM/YYYY') as Date,

                floor(ModifiedOn) as Date,

          //      Date(daystart(ModifiedOn)),

          //    OwningBusinessUnit,

          //    OwningUser,

          //    PriorityCode,

          //    RegardingObjectId,

          //    RegardingObjectIdDsc,

                RegardingObjectIdName,

          //    RegardingObjectIdYomiName,

          //    RegardingObjectTypeCode,

          //    ScheduledDurationMinutes,

          //    ScheduledEnd,

          //    ScheduledStart,

          //    ServiceId,

          //    StateCode,

              StatusCode,

              Subject,

          //    TimeZoneRuleVersionNumber,

          //    UTCConversionTimeZoneCode,

          //    VersionNumber,

              OwnerId,

              OwnerIdName,

          //    OwnerIdYomiName,

          //    OwnerIdDsc,

              OwnerIdType,

              ActivityId,

          //    PhoneNumber,

          //    DirectionCode,

          //    Category,

          //    Subcategory,

          //    ImportSequenceNumber,

          //    OverriddenCreatedOn,

          //    SubscriptionId,

              "JSA_CallOutcome",

               1     as CallOutcomeCount,

          //    "JSA_CallOutcomeWorkflow",

          //    "JSA_ComplaintCause",

          //    "JSA_FollowUpCallDate",

          //    "JSA_IsScheduledCall",

          //    "JSA_legacysystemid",

          //    "JSA_migrationcode",

          //    "JSA_NotGoingAheadCategory",

          //    "JSA_NotGoingAheadReason",

          //    "JSA_SMSWebServiceResponse",

              "JSA_Type",

          //    "jsa_notgoingaheadcompetitorid",

              "jsa_smsmessagetemplateid";

          SQL SELECT *

          FROM "JSAGroupUAT2_MSCRM".dbo.PhoneCall;

          • How to fix a date/data link problem and then restrict data?
            swuehl

            If you are using WHERE in an SQL SELECT statement, you can only use statements that are supported by your SQL connector. You cannot use all QV functions. So you need to check the correct SQL syntax for your date restriction.

             

            Some general comments (e.g. if you use where in a QV load, e.g. a resident load): A date is number (with zero point 1899-12-30), if you want to get the year of that date / number, you need to use year() function.

            If you want to compare dates (e.g. Date >= $(vMinDate) ), most issues arise from problems with differing date formats, so assure that the input date format matches your value you want to compare against.

            If you want to restrict by comparing to character, you need to use a QV operator like LIKE or a function like wildmatch() if your value contains wildcards.