Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
simongoodman
Creator
Creator

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

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,

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

6 Replies
swuehl
MVP
MVP

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

simongoodman
Creator
Creator
Author

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

swuehl
MVP
MVP

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

simongoodman
Creator
Creator
Author

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;

swuehl
MVP
MVP

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.

simongoodman
Creator
Creator
Author

Thanks for your help, as you might have guessed I am new to QV.