Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Interval match + Data model issue

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..




1 Reply
Highlighted
Creator III
Creator III

Hi,

Any inputs for the query posted.

Thanks..