//sundars4 - added as part of FCR Project
NoConcatenate
Fact_Table:
LOAD *
resident
Fact
;
// Limit Fact Table to drop competitors.
left join (Fact_Table)
load Distinct
[Brand Code],
[Competitor Flag]
resident
[Brand]
;
// Left Join Fact Table with [Sold To Customer] Table with key as _FF|CUST|ADDR, to get [Sold To Opt Out Flag] flags in the Fact Table.
// Sold To Opt Out Fact Records will be dropped.
left join (Fact_Table)
load
_FF|CUST|ADDR,
[Sold To Opt Out Flag],
[Organization Name]
resident
[Sold To Customer];
// Left Join Fact Table with [Date-Month] Table with key as Month-Year, to get CM,CQ,CHY,R3,R6,R12,R3YOY,R6YOY,YTDYOY flags,
// These Flags used to identify the current month,current quarter,current semester,rolling current quarter,rolling previous quarter,
// rolling current semester,rolling previous semester, rolling current year,rolling previous year,yoy current quarter,yoy previous quarter,
// yoy current semester,yoy previous semester,yoy current year and yoy previous year Fact Records for further rollup.
left join (Fact_Table)
load
*
resident
[Date-Month];
// Summarize Fact Table at Brand Code, _CURR_TERR_DIM_ID Level and get Current Month,Current Quarter,Current Semester PTP Metrics,
// Rolling 3 months,6 months, 12 Months metrics
// YOY 3 months,6 months and 12 Months metrics
sum:
Load
[Brand Code],
_CURR_TERR_DIM_ID,
[Competitor Flag],
//[Organization Name],
Sum(IF(R3='C',[Monthly Sales])) as RG_R3_C,
Sum(IF(R3='P',[Monthly Sales])) as RG_R3_P,
Sum(IF(R6='C',[Monthly Sales])) as RG_R6_C,
Sum(IF(R6='P',[Monthly Sales])) as RG_R6_P,
Sum(IF(R12='C',[Monthly Sales])) as RG_R12_C,
Sum(IF(R12='P',[Monthly Sales])) as RG_R12_P,
Sum(IF(R3='C',[Monthly Sales])) as RC_R3_C,
Sum(IF(R3='C',[Monthly Sales])) as RV_R3_C,
Sum(IF(R6='C',[Monthly Sales])) as RV_R6_C,
Sum(IF(R12='C',[Monthly Sales])) as RV_R12_C,
//Sum(IF(R3='C',[Competitor Flag] = 'N',[Monthly Sales])) as CM_R3_C,
//Sum(IF(R3='P',[Competitor Flag] = 'N',[Monthly Sales])) as CM_R3_P,
Sum(IF(R6='C' and [Competitor Flag]='N' ,[Monthly Sales])) as CM_R6_C,
Sum(IF(R6='P' and [Competitor Flag]='N',[Monthly Sales])) as CM_R6_P,
//Sum(IF(R12='C',[Competitor Flag] = 'N',[Monthly Sales])) as CM_R12_C,
//Sum(IF(R12='P',[Competitor Flag] = 'N',[Monthly Sales])) as CM_R12_P,
//Sum(IF(R3='C',[Competitor Flag] <> 'N',[Monthly Competitor Sales])) as CMC_R3_C,
//Sum(IF(R3='P',[Competitor Flag] <> 'N',[Monthly Competitor Sales])) as CMC_R3_P,
Sum(IF(R6='C' and [Competitor Flag]='Y',[Monthly Competitor Sales])) as CMC_R6_C,
Sum(IF(R6='P' and [Competitor Flag]='Y',[Monthly Competitor Sales])) as CMC_R6_P,
//Sum(IF(R12='C',[Competitor Flag] <> 'N',[Monthly Competitor Sales])) as CMC_R12_C,
//Sum(IF(R12='P',[Competitor Flag] <> 'N',[Monthly Competitor Sales])) as CMC_R12_P,
Sum(IF(R3YOY = 'C',[Monthly Sales])) as YOY_R3_C,
Sum(IF(R3YOY = 'P',[Monthly Sales])) as YOY_R3_P,
Sum(IF(R6YOY = 'C',[Monthly Sales])) as YOY_R6_C,
Sum(IF(R6YOY = 'P',[Monthly Sales])) as YOY_R6_P,
Sum(IF(YTDYOY = 'C',[Monthly Sales])) as YOY_R12_C,
Sum(IF(YTDYOY = 'P',[Monthly Sales])) as YOY_R12_P
resident
Fact_Table
where [Sold To Opt Out Flag] = 'N'
GROUP BY
[Brand Code],_CURR_TERR_DIM_ID,[Competitor Flag]
//,[Organization Name]
;
DROP TABLE Fact_Table;
// Left Join Fact Table with Territory Dimension Table with key as _CURR_TERR_DIM_ID to get [Region Number], [Division Number],[Territory Number].
left join(sum)
Load
Distinct
_CURR_TERR_DIM_ID,
[Region Number],
[Division Number],
[Territory Number],
[Field Force Code],
[Territory Name]
Resident
Territory;
// Get Distinct Brand Code, Brand Code_Region Number, Brand Code_Division Number and Brand Code_Territory Number from Fact Table to build the CSV File.
CSV_Table:
Load
Distinct
[Brand Code],
//[Brand Code] &'_' & [Organization Name] as BC_O,
[Brand Code] &'_' & [Region Number] as BC_R,
[Brand Code] &'_' & [Division Number] as BC_D,
[Brand Code] &'_' & [Territory Number] as BC_T,
[Brand Code] &'_' & [Field Force Code] as BC_FFC
Resident
sum;
left join(CSV_Table)
Load
[Brand Code],
IF(IsNull(Sum(RG_R3_P)) OR Sum(RG_R3_P) = 0 ,Null(),Round(((Sum(RG_R3_C)-Sum(RG_R3_P))/Sum(RG_R3_P) * 100),0.1)) AS RG_R3_FRAN,
IF(IsNull(Sum(RG_R6_P)) OR Sum(RG_R6_P) = 0 ,Null(),Round(((Sum(RG_R6_C)-Sum(RG_R6_P))/Sum(RG_R6_P) * 100),0.1)) AS RG_R6_FRAN,
IF(IsNull(Sum(RG_R12_P)) OR Sum(RG_R12_P) = 0 ,Null(),Round(((Sum(RG_R12_C)-Sum(RG_R12_P))/Sum(RG_R12_P) * 100),0.1)) AS RG_R12_FRAN,
IF(IsNull(Sum(YOY_R3_P)) OR Sum(YOY_R3_P) = 0 ,Null(),Round(((Sum(YOY_R3_C)-Sum(YOY_R3_P))/Sum(YOY_R3_P) * 100),0.1)) AS YOY_R3_FRAN,
IF(IsNull(Sum(YOY_R6_P)) OR Sum(YOY_R6_P) = 0 ,Null(),Round(((Sum(YOY_R6_C)-Sum(YOY_R6_P))/Sum(YOY_R6_P) * 100),0.1)) AS YOY_R6_FRAN,
IF(IsNull(Sum(YOY_R12_P)) OR Sum(YOY_R12_P) = 0 ,Null(),Round(((Sum(YOY_R12_C)-Sum(YOY_R12_P))/Sum(YOY_R12_P) * 100),0.1)) AS YOY_R12_FRAN,
sum(RV_R3_C) as RVO_R3_C_FRAN,
sum(RV_R6_C) as RVO_R6_C_FRAN,
sum(RV_R12_C) as RVO_R12_C_FRAN,
IF([Competitor Flag]='N',
IF(
SUM(CM_R6_C)=0, 0,
IF(
SUM(CM_R6_P)=0, 1,
SUM(CM_R6_C)/SUM(CM_R6_P)
)
),
IF(
SUM(CMC_R6_C)=0, 0,
IF(
SUM(CMC_R6_P)=0, 1,
SUM(CMC_R6_C)/SUM(CMC_R6_P)
)
)
)
AS COMP_FRAN
resident
sum
GROUP BY
[Brand Code],[Competitor Flag]
;
Temp:
LOAD
[Brand Code],RC_R3_C as Total
Resident sum;
Temp1:
Load
[Brand Code],[Territory Name],sum(RC_R3_C) as TotalTerritory
Resident sum
Group by [Territory Name];
left join(CSV_Table)
Load
[Brand Code] &'_' & [Region Number] as BC_R,
IF(IsNull(Sum(RG_R3_P)) OR Sum(RG_R3_P) = 0 ,Null(),Round(((Sum(RG_R3_C)-Sum(RG_R3_P))/Sum(RG_R3_P) * 100),0.1)) AS RG_R3_REG,
IF(IsNull(Sum(RG_R6_P)) OR Sum(RG_R6_P) = 0 ,Null(),Round(((Sum(RG_R6_C)-Sum(RG_R6_P))/Sum(RG_R6_P) * 100),0.1)) AS RG_R6_REG,
IF(IsNull(Sum(RG_R12_P)) OR Sum(RG_R12_P) = 0 ,Null(),Round(((Sum(RG_R12_C)-Sum(RG_R12_P))/Sum(RG_R12_P) * 100),0.1)) AS RG_R12_REG,
IF(IsNull(Sum(YOY_R3_P)) OR Sum(YOY_R3_P) = 0 ,Null(),Round(((Sum(YOY_R3_C)-Sum(YOY_R3_P))/Sum(YOY_R3_P) * 100),0.1)) AS YOY_R3_REG,
IF(IsNull(Sum(YOY_R6_P)) OR Sum(YOY_R6_P) = 0 ,Null(),Round(((Sum(YOY_R6_C)-Sum(YOY_R6_P))/Sum(YOY_R6_P) * 100),0.1)) AS YOY_R6_REG,
IF(IsNull(Sum(YOY_R12_P)) OR Sum(YOY_R12_P) = 0 ,Null(),Round(((Sum(YOY_R12_C)-Sum(YOY_R12_P))/Sum(YOY_R12_P) * 100),0.1)) AS YOY_R12_REG,
sum(RV_R3_C) as RVO_R3_C_REG,
sum(RV_R6_C) as RVO_R6_C_REG,
sum(RV_R12_C) as RVO_R12_C_REG
resident
sum
GROUP BY
[Brand Code],[Region Number]
;
left join(CSV_Table)
Load
[Brand Code] &'_' & [Division Number] as BC_D,
IF(IsNull(Sum(RG_R3_P)) OR Sum(RG_R3_P) = 0 ,Null(),Round(((Sum(RG_R3_C)-Sum(RG_R3_P))/Sum(RG_R3_P) * 100),0.1)) AS RG_R3_DIV,
IF(IsNull(Sum(RG_R6_P)) OR Sum(RG_R6_P) = 0 ,Null(),Round(((Sum(RG_R6_C)-Sum(RG_R6_P))/Sum(RG_R6_P) * 100),0.1)) AS RG_R6_DIV,
IF(IsNull(Sum(RG_R12_P)) OR Sum(RG_R12_P) = 0 ,Null(),Round(((Sum(RG_R12_C)-Sum(RG_R12_P))/Sum(RG_R12_P) * 100),0.1)) AS RG_R12_DIV,
IF(IsNull(Sum(YOY_R3_P)) OR Sum(YOY_R3_P) = 0 ,Null(),Round(((Sum(YOY_R3_C)-Sum(YOY_R3_P))/Sum(YOY_R3_P) * 100),0.1)) AS YOY_R3_DIV,
IF(IsNull(Sum(YOY_R6_P)) OR Sum(YOY_R6_P) = 0 ,Null(),Round(((Sum(YOY_R6_C)-Sum(YOY_R6_P))/Sum(YOY_R6_P) * 100),0.1)) AS YOY_R6_DIV,
IF(IsNull(Sum(YOY_R12_P)) OR Sum(YOY_R12_P) = 0 ,Null(),Round(((Sum(YOY_R12_C)-Sum(YOY_R12_P))/Sum(YOY_R12_P) * 100),0.1)) AS YOY_R12_DIV,
sum(RV_R3_C) as RVO_R3_C_DIV,
sum(RV_R6_C) as RVO_R6_C_DIV,
sum(RV_R12_C) as RVO_R12_C_DIV
resident
sum
GROUP BY
[Brand Code],[Division Number];
left join(CSV_Table)
Load
[Brand Code] &'_'& [Territory Number] as BC_T,
IF(IsNull(Sum(RG_R3_P)) OR Sum(RG_R3_P) = 0 ,Null(),Round(((Sum(RG_R3_C)-Sum(RG_R3_P))/Sum(RG_R3_P) * 100),0.1)) AS RG_R3_TERR,
IF(IsNull(Sum(RG_R6_P)) OR Sum(RG_R6_P) = 0 ,Null(),Round(((Sum(RG_R6_C)-Sum(RG_R6_P))/Sum(RG_R6_P) * 100),0.1)) AS RG_R6_TERR,
IF(IsNull(Sum(RG_R12_P)) OR Sum(RG_R12_P) = 0 ,Null(),Round(((Sum(RG_R12_C)-Sum(RG_R12_P))/Sum(RG_R12_P) * 100),0.1)) AS RG_R12_TERR,
IF(IsNull(Sum(YOY_R3_P)) OR Sum(YOY_R3_P) = 0 ,Null(),Round(((Sum(YOY_R3_C)-Sum(YOY_R3_P))/Sum(YOY_R3_P) * 100),0.1)) AS YOY_R3_TERR,
IF(IsNull(Sum(YOY_R6_P)) OR Sum(YOY_R6_P) = 0 ,Null(),Round(((Sum(YOY_R6_C)-Sum(YOY_R6_P))/Sum(YOY_R6_P) * 100),0.1)) AS YOY_R6_TERR,
IF(IsNull(Sum(YOY_R12_P)) OR Sum(YOY_R12_P) = 0 ,Null(),Round(((Sum(YOY_R12_C)-Sum(YOY_R12_P))/Sum(YOY_R12_P) * 100),0.1)) AS YOY_R12_TERR,
sum(RV_R3_C) as RVO_R3_C_TERR,
sum(RV_R6_C) as RVO_R6_C_TERR,
sum(RV_R12_C) as RVO_R12_C_TERR
resident
sum
GROUP BY
[Brand Code],[Territory Number]
;
// Drop Unwanted fields and Rename the fields as required in the export csv layout.
CSV_File:
Load
*,
[Brand Code] as Brand,
subfield(BC_T,'_',2) as Territory,
subfield(BC_D,'_',2) as Division,
subfield(BC_R,'_',2) as Region
//subfield(BC_O,'_',2) as Organisation
resident CSV_Table;
DROP Fields BC_D,BC_R,BC_T,BC_FFC;
Drop Tables sum,CSV_Table;
RENAME Table CSV_File to Fact_Agg;
NoConcatenate
CSV_File_Temp:
Load *
Resident
Fact_Agg
;
left join(CSV_File_Temp)
Load
[Brand Code],
[Brand Name] as Brand_Name
Resident
[Brand]
;
new_csv:
Load
Distinct
Territory as TERR_NBR,
Brand_Name as BRAND,
//Organisation as ACCOUNT,
//Brand as BRAND,
RG_R3_FRAN,
RG_R6_FRAN,
RG_R12_FRAN,
RG_R3_REG,
RG_R6_REG,
RG_R12_REG,
RG_R3_DIV,
RG_R6_DIV,
RG_R12_DIV,
RG_R3_TERR,
RG_R6_TERR,
RG_R12_TERR,
RVO_R3_C_FRAN,
RVO_R6_C_FRAN,
RVO_R12_C_FRAN,
RVO_R3_C_REG,
RVO_R6_C_REG,
RVO_R12_C_REG,
RVO_R3_C_DIV,
RVO_R6_C_DIV,
RVO_R12_C_DIV,
RVO_R3_C_TERR,
RVO_R6_C_TERR,
RVO_R12_C_TERR,
YOY_R3_FRAN,
YOY_R6_FRAN,
YOY_R12_FRAN,
YOY_R3_REG,
YOY_R6_REG,
YOY_R12_REG,
YOY_R3_DIV,
YOY_R6_DIV,
YOY_R12_DIV,
YOY_R3_TERR,
YOY_R6_TERR,
YOY_R12_TERR,
COMP_FRAN,
CONT1
resident CSV_File_Temp
order by Brand_Name,Territory
//order by Brand,Territory
;
Drop Table CSV_File_Temp;
store new_csv into \\Qliksb01\qlikview\Document\SourceDocuments\QVD Generators\FOIM\new.csv (txt);
DROP TABLE new_csv;
exit script;
