topic Re: how to write this expression in script in QlikView

how to write this expression in script

Wed, 11 Feb 2015 05:47:57 GMT

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

Re: how to write this expression in script

avinashelite — Wed, 11 Feb 2015 06:23:52 GMT

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

Re: how to write this expression in script

Wed, 11 Feb 2015 06:37:09 GMT

Is it working in script editor

Re: how to write this expression in script

anbu1984 — Wed, 11 Feb 2015 06:48:40 GMT

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

avinashelite — Wed, 11 Feb 2015 06:53:06 GMT

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

Re: how to write this expression in script

Wed, 11 Feb 2015 07:00:05 GMT

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

avinashelite — Wed, 11 Feb 2015 07:10:36 GMT

Hi,

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

Re: how to write this expression in script

Wed, 11 Feb 2015 07:12:48 GMT

hi ,how to use rank function in load statement

Re: how to write this expression in script

anbu1984 — Wed, 11 Feb 2015 07:30:22 GMT

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

Wed, 11 Feb 2015 08:14:48 GMT

thanks for reply anbu will apply n let u know

Re: how to write this expression in script

Wed, 11 Feb 2015 08:29:31 GMT

Hi anbu ,

it is not working it shows invaid expression

Re: how to write this expression in script

anbu1984 — Wed, 11 Feb 2015 08:34:40 GMT

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

Re: how to write this expression in script

Wed, 11 Feb 2015 08:38:20 GMT

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

anbu1984 — Wed, 11 Feb 2015 09:18:08 GMT

Can you post entire script?

Re: how to write this expression in script

Wed, 11 Feb 2015 09:21:33 GMT

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

anbu1984 — Wed, 11 Feb 2015 09:28:57 GMT

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

Wed, 11 Feb 2015 09:50:53 GMT

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 Name|ID]=,[Sold To Opt Out Flag]={'N'}>}[Monthly Sales]), 4, 1)),[Organization Name|ID])

same i have to write in a script editor

Re: how to write this expression in script

anbu1984 — Wed, 11 Feb 2015 09:54:55 GMT

Temp:

Load [Organization Name|ID],SUM([Monthly Sales]) MonthlySales

Resident Table

Where R6='C'

Group by [Organization Name|ID]

Rank:

Load *,RowNo() As Rank Resident Temp Order by MonthlySales desc