
Re: how to write this expression in script
Avinash R Feb 11, 2015 1:23 AM (in response to karthikeyan selvaraj)if(R3='C' and [Sold To Opt Out Flag]='N',Aggr(sum([Monthly Sales]),[Territory Name]))

Re: how to write this expression in script
karthikeyan selvaraj Feb 11, 2015 1:37 AM (in response to Avinash R )Is it working in script editor

Re: how to write this expression in script
anbu cheliyan Feb 11, 2015 1:48 AM (in response to karthikeyan selvaraj)Load [Territory Name],SUM([Monthly Sales])
Resident Table
Where R3='C' and [Sold To Opt Out Flag]='N'
Group by [Territory Name]

Re: how to write this expression in script
karthikeyan selvaraj Feb 11, 2015 2:00 AM (in response to anbu cheliyan)Thanks for your reply
But the actual expression is
=AGGR(NUM(RANK(SUM({<R6={'C'},[Organization Name]=,[Organization ID]=,[Organization NameID]=,[Sold To Opt Out Flag]={'N'}>}[Monthly Sales]), 4, 1)),[Organization NameID])
the above is the script i am trying to do in script editor

Re: how to write this expression in script
Avinash R Feb 11, 2015 2:10 AM (in response to karthikeyan selvaraj)Hi,
set analysis wont work in the script . Please follow the anbu approach

Re: how to write this expression in script
karthikeyan selvaraj Feb 11, 2015 2:12 AM (in response to Avinash R )hi ,how to use rank function in load statement

Re: how to write this expression in script
anbu cheliyan Feb 11, 2015 2:30 AM (in response to karthikeyan selvaraj)Temp:
Load [Territory Name],SUM([Monthly Sales]) MonthlySales
Resident Table
Where R3='C' and [Sold To Opt Out Flag]='N'
Group by [Territory Name]
Rank:
Load *,RowNo() As Rank Resident Temp Order by MonthlySales desc

Re: how to write this expression in script
karthikeyan selvaraj Feb 11, 2015 3:14 AM (in response to anbu cheliyan)thanks for reply anbu will apply n let u know

Re: how to write this expression in script
karthikeyan selvaraj Feb 11, 2015 3:29 AM (in response to karthikeyan selvaraj)Hi anbu ,
it is not working it shows invaid expression

Re: how to write this expression in script
anbu cheliyan Feb 11, 2015 3:34 AM (in response to karthikeyan selvaraj)Can you post what you tried? On which line you got error?

Re: how to write this expression in script
karthikeyan selvaraj Feb 11, 2015 3:38 AM (in response to anbu cheliyan)Let me tell you clealy forget about rank now,
expression in chart,
SUM({<R3={'C'}>}[Monthly Sales])
/SUM({<R3={'C'}>}TOTAL <[Territory Name]> [Monthly Sales])
The above expression i have to write in script editor
FYI,
R3=RC_R3_C
RC_R3_C=sum(monthly sales)

Re: how to write this expression in script
anbu cheliyan Feb 11, 2015 4:18 AM (in response to karthikeyan selvaraj)Can you post entire script?

Re: how to write this expression in script
karthikeyan selvaraj Feb 11, 2015 4:21 AM (in response to anbu cheliyan)//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 _FFCUSTADDR, 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
_FFCUSTADDR,
[Sold To Opt Out Flag],
[Organization Name]
resident
[Sold To Customer];
// Left Join Fact Table with [DateMonth] Table with key as MonthYear, 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
[DateMonth];
// 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;

Re: how to write this expression in script
anbu cheliyan Feb 11, 2015 4:28 AM (in response to karthikeyan selvaraj)On which line are you getting error?
You missed [Brand Code] in your group by in this table.
Temp1:
Load
[Brand Code],[Territory Name],sum(RC_R3_C) as TotalTerritory
Resident sum
Group by [Territory Name];

Re: how to write this expression in script
karthikeyan selvaraj Feb 11, 2015 4:50 AM (in response to anbu cheliyan)s its working fine now thanks anbu if you don't mind
can we see rank exp
=AGGR(NUM(RANK(SUM({<R6={'C'},[Organization Name]=,[Organization ID]=,[Organization NameID]=,[Sold To Opt Out Flag]={'N'}>}[Monthly Sales]), 4, 1)),[Organization NameID])
same i have to write in a script editor

Re: how to write this expression in script
anbu cheliyan Feb 11, 2015 4:54 AM (in response to karthikeyan selvaraj)Temp:
Load [Organization NameID],SUM([Monthly Sales]) MonthlySales
Resident Table
Where R6='C'
Group by [Organization NameID]
Rank:
Load *,RowNo() As Rank Resident Temp Order by MonthlySales desc














Re: how to write this expression in script
Avinash R Feb 11, 2015 1:53 AM (in response to karthikeyan selvaraj)if you want the sum then you need to create a resident load and use the group by function

