Interval match + Data model issue

[nareshthavidishetty]

Hi,

I have need to get number of [# SALES Open],[# SALES Initiated] and [# SALES Closed] for every month end.

I need to use the Interval match.But not able to get the right numbers.

Please check the below script.

Fact_Mapping:

LOAD
AutoNumberHash256(trim(text(Sales_Id)),'%SALES.KEY')    as [%SALES.KEY ,Sales_Started                                  as [SC# SALES Initiated]
,Closed                                     as [SC# SALES Closed]  
,text(Sales_Id)                           as [%SALES_ID.KEY]
,date(Calendar_Date,'DD-MMM-YYYY') as [Calendar Date]

,1 as [# SALES Open]
,0 as [# SALES Initiated]
,0 as [# SALES Closed]
FROM
[..\5_QVD\F_scr_Sales_Handling.qvd]
(qvd);


Left Join(Fact_Mapping)

SALESDim:

LOAD
AutoNumberHash256(trim(text(Sales_Id)),'%SALES.KEY')                  as [%SALES.KEY]

,date(Floor(Start_Date),'DD-MMM-YYYY')                         as [Start Date]
,date(Floor(Closure_Date),'DD-MMM-YYYY')                  as [Closure Date]

 
FROM
[..\5_QVD\D_scr_SALES1.qvd]
(qvd) where Last_Version='Y' ;

Fact_Mapping_Primary:

load *,

if(num(date([Closure Date],'DD-MMM-YYYY'))>1, date([Closure Date]-1,'DD-MMM-YYYY'), date(today(),'DD-MMM-YYYY')) as [Last Open Date]

,[Start Date] & '|' & if(num([Closure Date])>1, date([Closure Date]-1,'DD-MMM-YYYY'), date(today(),'DD-MMM-YYYY')) as IntervalKey   // subtracting one day to not generate a SACR Open count when on that day it is closed as well.

resident Fact_Mapping   where not(IsNull([%SALES.KEY]));

drop table Fact_Mapping;



TempInterval:

intervalmatch([Calendar Date])

load
[Start Date] as IntervalStart, 
[Last Open Date] as IntervalClose
resident Fact_Mapping_Primary;

left join(Fact_Mapping_Primary)

TempInterval2:

load distinct
[Calendar Date]
,date([Calendar Date],'DD-MMM-YYYY')       as [Calendar Date Target]

,IntervalStart & '|' & if(num(IntervalClose)>1, date(IntervalClose-1,'DD-MMM-YYYY'), date(today(),'DD-MMM-YYYY')) as IntervalKey

                   
,num([Calendar Date]) - num(IntervalStart) as [Sales Age],  

if(num([Calendar Date]) - num(IntervalStart)<=30, '0 TO 30 DAYS',
if(num([Calendar Date]) - num(IntervalStart)<=60, '31 TO 60 DAYS',
if(num([Calendar Date]) - num(IntervalStart)<=75, '61 TO 75 DAYS',
if(num([Calendar Date]) - num(IntervalStart)<=90, '76 TO 90 DAYS',
if(num([Calendar Date]) - num(IntervalStart)<=180, '91 TO 180 DAYS',
if(num([Calendar Date]) - num(IntervalStart)<=365, '181 TO 365 DAYS',
if(num([Calendar Date]) - num(IntervalStart)> 365,'>365 DAYS','UNKNOWN')))))))    as [SALES Age Bucket],



if(num([Calendar Date]) - num(IntervalStart)>90, 1,0) as [SALES Age 90+ Days]

resident TempInterval

where [Calendar Date]=Floor(MonthEnd([Calendar Date])) OR [Calendar Date]=Date(Floor(Today()-1),'DD-MMM-YYYY') ;  // Only check on Open SALES on last day of the month or Yesterday

drop table TempInterval;


concatenate(Fact_Mapping_Primary)

Innitiated: //load Innitiated QN date

load

[%SALES.KEY]  ,
0 as [# SALES Open],
0 as [# SALES Closed],
//[# SALES Initiated],
[SC# SALES Initiated] as  [# SALES Initiated],     // This is the variable to use for counting the SALESs
[Start Date] as [Calendar Date],
date([Calendar Date],'DD-MMM-YYYY')       as [Calendar Date Target],
num(if([Closure Date]>1, [Closure Date], $(vS_Current_Date))) - num([Start Date])  as [Sales Age],

if( num(if([Closure Date]>1, [Closure Date], $(vS_Current_Date))) -num([Start Date])<=30, '0 TO 30 DAYS',
if( num(if([Closure Date]>1, [Closure Date], $(vS_Current_Date)))- num([Start Date])<=60, '31 TO 60 DAYS',
if( num(if([Closure Date]>1, [Closure Date], $(vS_Current_Date))) - num([Start Date])<=75, '61 TO 75 DAYS',
if( num(if([Closure Date]>1, [Closure Date], $(vS_Current_Date))) - num([Start Date])<=90, '76 TO 90 DAYS',
if( num(if([Closure Date]>1, [Closure Date], $(vS_Current_Date))) - num([Start Date])<=180, '91 TO 180 DAYS',
if( num(if([Closure Date]>1, [Closure Date], $(vS_Current_Date)))  - num([Start Date])<=365, '181 TO 365 DAYS' ,

if( num(if([Closure Date]>1, [Closure Date], $(vS_Current_Date)))  - num([Start Date])>365,'>365 DAYS','UNKNOWN')))))))as [SALES Age Bucket]

resident Fact_Mapping_Primary;

concatenate (Fact_Mapping_Primary)

Closed: //load Closed QN date

load

[%SALES.KEY],
0 as [# SALES Open],
0 as [# SALES Initiated],
1 as [# SALES Closed],
[Closure Date] as [Calendar Date],
date([Calendar Date],'DD-MMM-YYYY')       as [Calendar Date Target],
num([Closure Date]) - num([Start Date])  as [Sales Age],

if([# SALES Closed] =1, if(num([Closure Date]) - num([Start Date])<=30, '0 TO 30 DAYS',
if(num([Closure Date]) - num([Start Date])<=60, '31 TO 60 DAYS',
if(num([Closure Date]) - num([Start Date])<=75, '61 TO 75 DAYS',
if(num([Closure Date]) - num([Start Date])<=90, '76 TO 90 DAYS',
if(num([Closure Date]) - num([Start Date])<=180, '91 TO 180 DAYS',
if(num([Closure Date]) - num([Start Date])<=365, '181 TO 365 DAYS',
if(num([Closure Date]) - num([Start Date])> 365,'>365 DAYS','UNKNOWN'))))))),'NOT APPLICABLE') as [SALES Age Bucket]

resident Fact_Mapping_Primary
where num([Closure Date])>1 ;

Thanks..

[nareshthavidishetty]

Hi,

Any inputs for the query posted.

Thanks..