Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Hi,
Any inputs for the query posted.
Thanks..