topic Re: How Do I Change the Date of a Record Using the Load Statement? in QlikView

How Do I Change the Date of a Record Using the Load Statement?

Tue, 09 Sep 2014 16:16:24 GMT

Hello,

I'm pulling my data from Salesforce.com. I'm trying to assign a date to specific records (Unallocated Revenues) when I load them. The date field on the record is 'Effective_Date__c' (and due to business rules, these specific records do not have an Effective Date so I'm trying to assign one just to classify them somewhere). I'm trying to give them an Effective Date = the last day of the current year.

Below is my load statement:

LOAD

Id as FactId,

Name as FactName,

Project__c as ProjectId,

Client__c as Client,

'' as [Task Value],

'Unallocated' as FactType,

'' as PFA,

Project_Name__c as ProjectName,

Project_Number__c as PR#,

'' as Program,

'' as Active,

IF(Revenue_Status__c = 'Unallocated', Effective_Date__c = Date(YearEnd(Today()))) as [Actual Date],

Amount__c as Amount;

When I reload, the Unallocated records display under December 1899.

Is there a way that I can load these records so the Effective Date = December 31, 2014?

Thanks,

Luke

Re: How Do I Change the Date of a Record Using the Load Statement?

JonnyPoole — Tue, 09 Sep 2014 16:19:34 GMT

Try just this.

IF(Revenue_Status__c = 'Unallocated', Date(YearEnd(Today())) as [Actual Date],

I was thinking you may want to assign the original date however if its not unallocated:

IF(Revenue_Status__c = 'Unallocated', Date(YearEnd(Today()),Effective_Date__c) as [Actual Date],

Re: How Do I Change the Date of a Record Using the Load Statement?

Tue, 09 Sep 2014 16:23:35 GMT

Hi Jonathan,

Thank you for your quick reply. I have added this to my IF statement and have received the same output.

Thanks,

Luke

Re: How Do I Change the Date of a Record Using the Load Statement?

JonnyPoole — Tue, 09 Sep 2014 16:36:51 GMT

hmm.. i missed an extra ')' to close the IF. Try it once more

IF(Revenue_Status__c = 'Unallocated', Date(YearEnd(Today()))) as [Actual Date],

Re: How Do I Change the Date of a Record Using the Load Statement?

Tue, 09 Sep 2014 17:56:35 GMT

No worries. I updated and now the Unallocated is not being bucketed by any date. I'm curious as to why previous is bucketing it under Year = 1899?

Thanks,

Luke

Re: How Do I Change the Date of a Record Using the Load Statement?

JonnyPoole — Tue, 09 Sep 2014 18:04:38 GMT

if it encounters a 'bad date' value is reverts it to that 1899 date. If it evaluates to NULL you'll get the '-'.

Can you paste in the full script ? I've tested this a number of times and can't think why the logic isn't working. That or a QVW upload/attachment.

Re: Re: How Do I Change the Date of a Record Using the Load Statement?

Tue, 09 Sep 2014 18:36:49 GMT

Hi Jonathan,

I've attached the QVW as well as the script here:

Main Tab:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET vToDate = Date(YearEnd(Today()), 'YYYYMMDD');

CUSTOM CONNECT TO "Provider=SalesForceDLL.dll;F26;XUserId=eQfMbSFMHDbKGXENODdKWTJOBDdSWXZNAKZCWTRNFDcEXXRMVbYIXUZOJDdSHXMMPLbGA;XPassword=YMeCUCFMHDbKWYIGTBNKTXA;";

Fact:

//Actual Revenue Load

LOAD

Id as FactId,

// Id as RevenueId,

Name as FactName,

// Milestone__c as MilestoneId,

Project__c as ProjectId,

'' as Program,

Client__c as Client,

'' as [Task Value],

'Booked' as FactType,

'' as PFA,

Project_Name__c as ProjectName,

Project_Number__c as PR#,

'' as Active,

Effective_Date__c as [Actual Date],

Amount__c as Amount;

// Amount__c as Revenue,

// '' as Booked,

// '' as SNS,

// '' as Unallocated;

SQL SELECT *

FROM Revenue__c

WHERE Record_Type_Name__c = 'Actual';

//Booked Revenue Load

LOAD

Id as FactId,

// Id as RevenueId,

Name as FactName,

// Milestone__c as MilestoneId,

Project__c as ProjectId,

'' as Program,

Client__c as Client,

'' as [Task Value],

'Booked' as FactType,

'' as PFA,

Project_Name__c as ProjectName,

Project_Number__c as PR#,

'' as Active,

Effective_Date__c as [Actual Date],

Amount__c as Amount;

// '' as Revenue,

// 'Amount__c' as Booked,

// '' as SNS,

// '' as Unallocated;

SQL SELECT *

FROM Revenue__c

WHERE Record_Type_Name__c = 'Forecast'

AND Revenue_Status__c = 'Planned';

//SNS Revenue Load

LOAD

Id as FactId,

// Id as RevenueId,

Name as FactName,

// Milestone__c as MilestoneId,

Project__c as ProjectId,

Client__c as Client,

'' as [Task Value],

'SNS' as FactType,

'' as PFA,

Project_Name__c as ProjectName,

Project_Number__c as PR#,

'' as Program,

'' as Active,

Effective_Date__c as [Actual Date],

Amount__c as Amount;

// '' as Revenue,

// '' as Booked,

// 'Amount__c' as SNS,

// '' as Unallocated;

SQL SELECT *

FROM Revenue__c

WHERE Record_Type_Name__c = 'Forecast'

AND Revenue_Status__c = 'Signed not Scheduled';

LOAD

Id as FactId,

// Id as RevenueId,

Name as FactName,

// Milestone__c as MilestoneId,

Project__c as ProjectId,

Client__c as Client,

'' as [Task Value],

'Unallocated' as FactType,

'' as PFA,

Project_Name__c as ProjectName,

Project_Number__c as PR#,

'' as Program,

'' as Active,

// Effective_Date__c = MAKEDATE(2014,12,31) as [Actual Date],

// '' as [Actual Date],

// IF(Revenue_Status__c = 'Unallocated', Effective_Date__c = Date(YearEnd(Today())),Effective_Date__c) as [Actual Date],

IF(Revenue_Status__c = 'Unallocated', Date(YearEnd(Today()))) as [Actual Date],

Amount__c as Amount;

// '' as Revenue,

// '' as Booked,

// 'Amount__c' as SNS,

// '' as Unallocated;

SQL SELECT *

FROM Revenue__c

WHERE Record_Type_Name__c = 'Forecast'

AND Revenue_Status__c = 'Unallocated';

LOAD

Id AS [FactID],

Id AS ProjectId,

'' as FactType,

Name AS [FactName],

Account_Text__c as Client,

PFA_Text__c as PFA,

// Project_Financial_Analyst__c AS [PFAID],

Project_Number__c AS [PR#],

Name as ProjectName,

pse__Is_Active__c AS [Active],

Program__c as [Program],

'' as Amount,

'' as [Task Value];

// '' as MilestoneId,

// '' as Revenue,

// '' as Booked,

// '' as SNS,

// '' as Unallocated,

// '' as [Actual Date];

SQL SELECT *

FROM pse__Proj__c

WHERE (NOT Name LIKE '%Template%')

AND (NOT Name LIKE '%Test%')

AND

(pse__Project_Type__c = 'Customer Project' OR (pse__Project_Type__c = 'Internal' and Unallocated_Project__c = True)); //Added Internal Projects w/ Unallocated Revenue = True on 6/13/14 by Luke F. to accommodate Mary Trant's request.

Master Calendar Tab:

/*************** MinMax Table *************

Keeps minimum and maximum Date value from Facts table

MinMax:

LOAD

Min([Actual Date]) as MinDate,

Max([Actual Date]) as MaxDate

RESIDENT Fact;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

/*************** Temporary Calendar *************

Generates a single table with one field containing

all existing dates between MinDate and MaxDate.

TempCal:

LOAD

date($(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

MasterCalendar:

LOAD

//TempDate as IslandDate,

//Date(TempDate,'MM/DD/YYYY') AS CaseDate,

Date(TempDate) AS [Actual Date],

Week(TempDate) AS Week,

Year(TempDate) AS Year,

Month(TempDate) AS Month,

Day(TempDate) AS Day,

Weekday(TempDate) AS WeekDay,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

//Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

//Date(TempDate, 'MMM-YYYY') AS MonthYear,

Month(TempDate)&'-'&Year(TempDate) AS MonthYear,

Year(TempDate)&'-'& num(month(TempDate)) AS YearMonth,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

// MonthStart = num(date(MonthStart(AddMonths(Today(),$(MonthsFromToday),'DD/MM/YYYY')))),

inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

Re: How Do I Change the Date of a Record Using the Load Statement?

JonnyPoole — Wed, 10 Sep 2014 00:05:04 GMT

i think your load is fine, because if you look at below, even though 'Year' is greyed out, it is actually associating to 12/31/2014 actual date. In the list box next to it that says 2014 at the top, the expression is year([Actual Date]) . I used the same expression to replace Year in the pivot table and it works there too.

so what is wrong ?

I think it may be your master calendar load:

try replacing this:

Date(TempDate) AS [Actual Date],

Week(TempDate) AS Week,

Year(TempDate) AS Year,

Month(TempDate) AS Month,

with

Date(TempDate) AS [Actual Date],

Week(Date(TempDate)) AS Week,

Year(Date(TempDate)) AS Year,

Month(Date(TempDate)) AS Month,

etc... just ot make sure it know TempDate is in fact a Date.