Interval match + data modeling issue

[nareshthavidishetty]

Hi,

I have used the interval match to get sales open,sales Initiated and sales closed count where all these values need to get on every month end.

But I'm not able to crack the scenario.

Attached is the script what i have tried.

Please let me know if script need changes.

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]       

,autonumber(text(Sales_Status_Id),'%Status ID')          as [%Status ID]
,autonumber(Supplier_Id,'%Supplier ID')             as [%Supplier ID]
,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_CAP_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_CAP_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..