Date Difference in Months not calculating for all rows

[BZ]

Hello,

I have a table where I've created two additional date columns to take care of gaps/nulls in the original 4 date columns like this in the Data Load Editor:

Load *,
((year([New End Date])*12)+month([New End Date])) - (((year([New Start Date])*12)+month([New Start Date]))) AS [New Term];

Load
Contract Start Date,
Contract End Date,
Item Start Date,
Item End Date,

Date(Date#(if(isnull([Rev. Rec. Start Date]),[Contract Item Start Date],[Rev. Rec. Start Date]), 'MM/DD/YYYY')) AS [New Start Date],
Date(Date#(If(IsNull([Rev. Rec. End Date]),[Contract Item End Date],[Rev. Rec. End Date]), 'MM/DD/YYYY')) AS [New End Date],

From ....

Unfortunately, for some reason the interval formula does not seem to work on all rows:

Could you help me figure out what's wrong here?

[agni_gold]

You can use

Interval ([New End Date] - [New Start date],'MM')

[sunny_talwar]

Do you have more than one data source in play here by any chance?

[BZ]

Hi,

There are 2 CSV files with identical columns which have been concatenated. Below is the data loader script, if that helps.

Set dataManagerTables = '','START','END';
//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables) 
    Let index = 0;
    Let currentName = name; 
    Let tableNumber = TableNumber(name); 
    Let matches = 0; 
    Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
        index = index + 1; 
        currentName = name & '-' & index; 
        tableNumber = TableNumber(currentName) 
        matches = Match('$(currentName)', $(dataManagerTables));
    Loop 
    If index > 0 then 
            Rename Table '$(name)' to '$(currentName)'; 
    EndIf; 
Next; 
Set dataManagerTables = ;


Unqualify *;

Load *,
 //Interval([New End Date]-[New Start Date],'m') AS [New Term];
((year([New End Date])*12)+month([New End Date])) - (((year([New Start Date])*12)+month([New Start Date]))) AS [New Term];

//([New End Date]-[New Start Date])/30.4 AS [New Term];

[START]:
LOAD
	Date(Date#([Invoice Date], 'MM/DD/YYYY') ) AS [Invoice Date],
	[Item],
	[Document Number],
	[Bill To Customer],
	[End User],
	[Description],
	[Sales Order Memo],
	[Quantity],
	[List Rate],
	[Inline Discount],
	[Amount],
    [Amount]/[Rev. Rec. Term in Months]*12 AS 'Annual Renewal Amount',
	[License ID],
	[Status],
	[Subsidiary],
	[Dedicated Account],
	[Dedicated Account Rep],
	Date(Date#([Rev. Rec. Start Date], 'MM/DD/YYYY' )) AS [Rev. Rec. Start Date],
	Date(Date#([Rev. Rec. End Date], 'MM/DD/YYYY') ) AS [Rev. Rec. End Date],
	[Rev. Rec. Term in Months],
	Date(Date#([Contract Item Start Date], 'MM/DD/YYYY') ) AS [Contract Item Start Date],
	Date(Date#([Contract Item End Date], 'MM/DD/YYYY') ) AS [Contract Item End Date],
	[Contract Item Term],
	Date(Date#([123Rev Rec Start Date], 'MM/DD/YYYY') ) AS [123Rev Rec Start Date],
	Date(Date#([123Rev Rec End Date], 'MM/DD/YYYY') ) AS [123Rev Rec End Date],
    Date(Date#(if(isnull([Rev. Rec. Start Date]),[Contract Item Start Date],[Rev. Rec. Start Date]), 'MM/DD/YYYY')) AS [New Start Date],
    Date(Date#(If(IsNull([Rev. Rec. End Date]),[Contract Item End Date],[Rev. Rec. End Date]), 'MM/DD/YYYY')) AS [New End Date],
    
    
    FileBaseName() AS [End/Start]
 FROM [lib://START.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

CONCATENATE ([START]) LOAD
	Date(Date#([Invoice Date], 'MM/DD/YYYY') ) AS [Invoice Date],
	[Item],
	[Document Number],
	[Bill To Customer],
	[End User],
	[Description],
	[Sales Order Memo],
	[Quantity],
	[List Rate],
	[Inline Discount],
	[Amount],
    [Amount]/[Rev. Rec. Term in Months]*12 AS 'Annual Renewal Amount',
	[License ID],
	[Status],
	[Subsidiary],
	[Dedicated Account],
	[Dedicated Account Rep],
	Date(Date#([Rev. Rec. Start Date], 'MM/DD/YYYY' ) ) AS [Rev. Rec. Start Date],
	Date(Date#([Rev. Rec. End Date], 'MM/DD/YYYY') ) AS [Rev. Rec. End Date],
	[Rev. Rec. Term in Months],
	Date(Date#([Contract Item Start Date], 'MM/DD/YYYY' )) AS [Contract Item Start Date],
	Date(Date#([Contract Item End Date], 'MM/DD/YYYY') ) AS [Contract Item End Date],
	[Contract Item Term],
	Date(Date#([123Rev Rec Start Date], 'MM/DD/YYYY') ) AS [123Rev Rec Start Date],
	Date(Date#([123Rev Rec End Date], 'MM/DD/YYYY') ) AS [123Rev Rec End Date],
    Date(Date#(if(isnull([Rev. Rec. Start Date]),[Contract Item Start Date],[Rev. Rec. Start Date]), 'MM/DD/YYYY')) AS [New Start Date],
    Date(Date#(If(IsNull([Rev. Rec. End Date]),[Contract Item End Date],[Rev. Rec. End Date]), 'MM/DD/YYYY')) AS [New End Date],
    
    FileBaseName() AS [End/Start]
 FROM [lib://END.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);






[autoCalendar]: 
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
  Month($1) AS [Month] Tagged ('$month', '$cyclic'),
  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
  Year(Today())-Year($1) AS [YearsAgo] ,
  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
  Month(Today())-Month($1) AS [MonthRelNo] ,
  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
  Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Maintenance Expiration Date],[Invoice Date], [Rev. Rec. Start Date], [Rev. Rec. End Date], [Contract Item Start Date], [START.Contract Item End Date], [123Rev Rec Start Date], [123Rev Rec End Date] USING [autoCalendar] ;

[BZ]

Hi,

I tried 3 different ways, but all of them left some gaps. You can see the full data loader script above.

[sunny_talwar · Accepted solution]

So, I guess your issue is that you are only doing a preceding load on one of the tables... the one which you are concatenating doesn't have a New Term getting calculated... you might think that the order of operations are first Concatenate and then do the preceding load... but it isn't... Try this

Set dataManagerTables = '','START','END';
//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables) 
    Let index = 0;
    Let currentName = name; 
    Let tableNumber = TableNumber(name); 
    Let matches = 0; 
    Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
        index = index + 1; 
        currentName = name & '-' & index; 
        tableNumber = TableNumber(currentName) 
        matches = Match('$(currentName)', $(dataManagerTables));
    Loop 
    If index > 0 then 
            Rename Table '$(name)' to '$(currentName)'; 
    EndIf; 
Next; 
Set dataManagerTables = ;


Unqualify *;

[START]:
Load *,
((year([New End Date])*12)+month([New End Date])) - (((year([New Start Date])*12)+month([New Start Date]))) AS [New Term];
LOAD
	Date(Date#([Invoice Date], 'MM/DD/YYYY') ) AS [Invoice Date],
	[Item],
	[Document Number],
	[Bill To Customer],
	[End User],
	[Description],
	[Sales Order Memo],
	[Quantity],
	[List Rate],
	[Inline Discount],
	[Amount],
    [Amount]/[Rev. Rec. Term in Months]*12 AS 'Annual Renewal Amount',
	[License ID],
	[Status],
	[Subsidiary],
	[Dedicated Account],
	[Dedicated Account Rep],
	Date(Date#([Rev. Rec. Start Date], 'MM/DD/YYYY' )) AS [Rev. Rec. Start Date],
	Date(Date#([Rev. Rec. End Date], 'MM/DD/YYYY') ) AS [Rev. Rec. End Date],
	[Rev. Rec. Term in Months],
	Date(Date#([Contract Item Start Date], 'MM/DD/YYYY') ) AS [Contract Item Start Date],
	Date(Date#([Contract Item End Date], 'MM/DD/YYYY') ) AS [Contract Item End Date],
	[Contract Item Term],
	Date(Date#([123Rev Rec Start Date], 'MM/DD/YYYY') ) AS [123Rev Rec Start Date],
	Date(Date#([123Rev Rec End Date], 'MM/DD/YYYY') ) AS [123Rev Rec End Date],
    Date(Date#(if(isnull([Rev. Rec. Start Date]),[Contract Item Start Date],[Rev. Rec. Start Date]), 'MM/DD/YYYY')) AS [New Start Date],
    Date(Date#(If(IsNull([Rev. Rec. End Date]),[Contract Item End Date],[Rev. Rec. End Date]), 'MM/DD/YYYY')) AS [New End Date],
    FileBaseName() AS [End/Start]
 FROM [lib://START.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

CONCATENATE ([START])
Load *,
((year([New End Date])*12)+month([New End Date])) - (((year([New Start Date])*12)+month([New Start Date]))) AS [New Term];
LOAD
	Date(Date#([Invoice Date], 'MM/DD/YYYY') ) AS [Invoice Date],
	[Item],
	[Document Number],
	[Bill To Customer],
	[End User],
	[Description],
	[Sales Order Memo],
	[Quantity],
	[List Rate],
	[Inline Discount],
	[Amount],
    [Amount]/[Rev. Rec. Term in Months]*12 AS 'Annual Renewal Amount',
	[License ID],
	[Status],
	[Subsidiary],
	[Dedicated Account],
	[Dedicated Account Rep],
	Date(Date#([Rev. Rec. Start Date], 'MM/DD/YYYY' ) ) AS [Rev. Rec. Start Date],
	Date(Date#([Rev. Rec. End Date], 'MM/DD/YYYY') ) AS [Rev. Rec. End Date],
	[Rev. Rec. Term in Months],
	Date(Date#([Contract Item Start Date], 'MM/DD/YYYY' )) AS [Contract Item Start Date],
	Date(Date#([Contract Item End Date], 'MM/DD/YYYY') ) AS [Contract Item End Date],
	[Contract Item Term],
	Date(Date#([123Rev Rec Start Date], 'MM/DD/YYYY') ) AS [123Rev Rec Start Date],
	Date(Date#([123Rev Rec End Date], 'MM/DD/YYYY') ) AS [123Rev Rec End Date],
    Date(Date#(if(isnull([Rev. Rec. Start Date]),[Contract Item Start Date],[Rev. Rec. Start Date]), 'MM/DD/YYYY')) AS [New Start Date],
    Date(Date#(If(IsNull([Rev. Rec. End Date]),[Contract Item End Date],[Rev. Rec. End Date]), 'MM/DD/YYYY')) AS [New End Date],
    
    FileBaseName() AS [End/Start]
 FROM [lib://END.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

[autoCalendar]: 
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
  Month($1) AS [Month] Tagged ('$month', '$cyclic'),
  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
  Year(Today())-Year($1) AS [YearsAgo] ,
  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
  Month(Today())-Month($1) AS [MonthRelNo] ,
  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
  Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Maintenance Expiration Date],[Invoice Date], [Rev. Rec. Start Date], [Rev. Rec. End Date], [Contract Item Start Date], [START.Contract Item End Date], [123Rev Rec Start Date], [123Rev Rec End Date] USING [autoCalendar] ;

[BZ]

Brilliant! Thank you so much, sunny_talwar!