17 Replies Latest reply: Feb 11, 2015 4:54 AM by anbu cheliyan RSS

    how to write this expression in script

      =SUM({<R3={'C'},[Sold To Opt Out Flag]={'N'}>}TOTAL <[Territory Name]> [Monthly Sales]

        • Re: how to write this expression in script
          Avinash R

          if(R3='C' and [Sold To Opt Out Flag]='N',Aggr(sum([Monthly Sales]),[Territory Name]))

            • Re: how to write this expression in script

              Is it working in script editor

                • Re: how to write this expression in script
                  anbu cheliyan

                  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

                      Thanks for your reply

                       

                      But the actual expression is

                       

                      =AGGR(NUM(RANK(SUM({<R6={'C'},[Organization Name]=,[Organization ID]=,[Organization Name|ID]=,[Sold To Opt Out Flag]={'N'}>}[Monthly Sales]), 4, 1)),[Organization Name|ID])

                       

                      the above is the script i am trying to do in script editor

                        • Re: how to write this expression in script
                          Avinash R

                          Hi,

                           

                          set analysis wont work in the script . Please follow the anbu approach

                            • Re: how to write this expression in script

                              hi ,how to use rank function in load statement

                                • Re: how to write this expression in script
                                  anbu cheliyan

                                  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

                                      thanks for reply anbu will apply n let u know

                                        • Re: how to write this expression in script

                                          Hi anbu ,

                                           

                                          it is not working it shows invaid expression

                                            • Re: how to write this expression in script
                                              anbu cheliyan

                                              Can you post what you tried? On which line you got error?

                                                • Re: how to write this expression in script

                                                  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

                                                      Can you post entire script?

                                                        • Re: how to write this expression in script

                                                          //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;

                                      • Re: how to write this expression in script
                                        Avinash R

                                        if you want  the sum then you need to create a resident load and use the group by function