grade in load script?

[#1]

Dear Expert,

Trying to create logical field in qlikview load script using below code and it's not working and getting below error. please advise. Thanks

Error in expression:

')' expected

Temp1:

Load *,

if(

  (Sum(TotalNetPrice)/)<0.6, 'A Stocks',

  if (

  (Sum(TotalNetPrice)/)>0.6 and (Sum(TotalNetPrice)/)<0.9,'B Stocks', 'C Stocks'

  )

  ) as Grade

Resident Sales

// -------------------------------------- Qlikview Script --------------------------------------------

Sales:

LOAD * INLINE [

    Country, Week, Category, Launch, Stock type, Article, SOH, Sold Qty Week, TotalNetPrice, TotalLandedCost, Type_ID, OrderQty

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270006, 400, 26, 80473, 31668, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-20780203, 316, 30, 77442, 24932, NB, 19

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80680430, 601, 33, 77371, 25368, RO, 154

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30741445, 504, 25, 77146, 21166, RO, 10

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780230, 504, 30, 74765, 31554, RO, 35

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50670452, 446, 99, 282234, 125982, NB, 8

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780219, 244, 102, 276768, 116025, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30780217, 487, 80, 244943, 91000, RO, 3

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80840056, 243, 100, 237215, 96610, RO, 2

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270010, 546, 75, 231134, 81417, RO, 6

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80150494, 242, 5, 10833, 4129, RO, 1

    Thailand, 38, Ladies Bag, 2015-L2, Seasonal, CK2-50780074, 288, 3, 9787, 3573, RO, 56

    Thailand, 38, Ladies Bag, 2016-L4 (JIT), Seasonal, CK2-80150527, 211, 6, 9703, 3786, RO, 20

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-20741422, 86, 4, 9319, 4114, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80741408, 211, 4, 9319, 3574, NB, 7

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670436, 159, 3, 9188, 4534, NB, 1

    Thailand, 38, Ladies Bag, 2016-L2, Seasonal, CK2-50680373, 200, 4, 8935, 3303, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670432, 74, 4, 8506, 4155, UB, 1

];

Sum_of_TotalNetPrice:

LOAD Sum(TotalNetPrice) as TotalNet Resident Sales;

Let vTotalNetPrice = peek('TotalNetPrice');

Temp1:

Load *,

if(

  (Sum(TotalNetPrice)/$(vTotalNetPrice))<0.6, 'A Stocks',

  if (

  (Sum(TotalNetPrice)/$(vTotalNetPrice))>0.6 and (Sum(TotalNetPrice)/$(vTotalNetPrice))<0.9,'B Stocks', 'C Stocks'

  )

  ) as Grade

Resident Sales;

Drop table Sales;

Thanks, d

[vcanale]

Hi, Try:

Temp1:

Load *,

if(Sum(TotalNetPrice)/$(vTotalNetPrice)<0.6, 'A Stocks',

  if (Sum(TotalNetPrice)/$(vTotalNetPrice)>0.6 and Sum(TotalNetPrice)/$(vTotalNetPrice)<0.9),'B Stocks',
         'C Stocks') as Grade

Resident Sales;

[devarasu07]

Sales:

LOAD * INLINE [

    Country, Week NO, Category, Launch, Stock type, Article, SOH, Sold Qty Week, TotalNetPrice, TotalLandedCost, Type_ID, Order Qty

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270006, 400, 26, 80473, 31668, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-20780203, 316, 30, 77442, 24932, NB, 19

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80680430, 601, 33, 77371, 25368, RO, 154

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30741445, 504, 25, 77146, 21166, RO, 10

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780230, 504, 30, 74765, 31554, RO, 35

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50670452, 446, 99, 282234, 125982, NB, 8

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780219, 244, 102, 276768, 116025, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30780217, 487, 80, 244943, 91000, RO, 3

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80840056, 243, 100, 237215, 96610, RO, 2

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270010, 546, 75, 231134, 81417, RO, 6

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80150494, 242, 5, 10833, 4129, RO, 1

    Thailand, 38, Ladies Bag, 2015-L2, Seasonal, CK2-50780074, 288, 3, 9787, 3573, RO, 56

    Thailand, 38, Ladies Bag, 2016-L4 (JIT), Seasonal, CK2-80150527, 211, 6, 9703, 3786, RO, 20

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-20741422, 86, 4, 9319, 4114, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80741408, 211, 4, 9319, 3574, NB, 7

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670436, 159, 3, 9188, 4534, NB, 1

    Thailand, 38, Ladies Bag, 2016-L2, Seasonal, CK2-50680373, 200, 4, 8935, 3303, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670432, 74, 4, 8506, 4155, UB, 1

];

temp:

load SUM(TotalNetPrice) as TotalSales Resident Sales;

let vtotal=Peek('TotalSales');

temp2:

load *,

if(

  (TotalNetPrice/'$(vtotal)')<0.05,'A Stocks',

  if (

  (TotalNetPrice/'$(vtotal)')>0.05 and (TotalNetPrice/'$(vtotal)')<0.13,'B Stocks','C Stocks'

  )

  )

as Grade

Resident Sales;

DROP Table Sales;

[maxgro]

this works but I'm not sure if you want something different: I removed the sum, I don't understand what you want to sum

Sales:

LOAD * INLINE [

    Country, Week, Category, Launch, Stock type, Article, SOH, Sold Qty Week, TotalNetPrice, TotalLandedCost, Type_ID, OrderQty

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270006, 400, 26, 80473, 31668, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-20780203, 316, 30, 77442, 24932, NB, 19

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80680430, 601, 33, 77371, 25368, RO, 154

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30741445, 504, 25, 77146, 21166, RO, 10

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780230, 504, 30, 74765, 31554, RO, 35

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50670452, 446, 99, 282234, 125982, NB, 8

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780219, 244, 102, 276768, 116025, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30780217, 487, 80, 244943, 91000, RO, 3

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80840056, 243, 100, 237215, 96610, RO, 2

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270010, 546, 75, 231134, 81417, RO, 6

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80150494, 242, 5, 10833, 4129, RO, 1

    Thailand, 38, Ladies Bag, 2015-L2, Seasonal, CK2-50780074, 288, 3, 9787, 3573, RO, 56

    Thailand, 38, Ladies Bag, 2016-L4 (JIT), Seasonal, CK2-80150527, 211, 6, 9703, 3786, RO, 20

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-20741422, 86, 4, 9319, 4114, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80741408, 211, 4, 9319, 3574, NB, 7

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670436, 159, 3, 9188, 4534, NB, 1

    Thailand, 38, Ladies Bag, 2016-L2, Seasonal, CK2-50680373, 200, 4, 8935, 3303, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670432, 74, 4, 8506, 4155, UB, 1

];

Sum_of_TotalNetPrice:

LOAD Sum(TotalNetPrice) as TotalNet Resident Sales;

Let vTotalNetPrice = peek('TotalNet');

trace vTotalNetPrice=$(vTotalNetPrice);

Temp1:

Load *,

if(  ((TotalNetPrice)/$(vTotalNetPrice)) < 0.6, 'A Stocks',

if( ((TotalNetPrice)/$(vTotalNetPrice)) > 0.6 and ((TotalNetPrice)/$(vTotalNetPrice)) <0.9, 'B Stocks', 'C Stocks'

  ))

as Grade

Resident Sales;

Drop table Sales;

[#5]

Hi,

Thanks for your kind support, it's working but is it possible to have cumulative % in load script?

Need to calculate Cumulative % = (TotalNetPrice)/SUM (TOTAL TotalNetprice). after derived Cumulative % value then i need to use it for Grade calculation,

Sales:

LOAD * INLINE [

    Country, Week NO, Category, Launch, Stock type, Article, SOH, Sold Qty Week, TotalNetPrice, TotalLandedCost, Type_ID, Order Qty

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270006, 400, 26, 80473, 31668, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-20780203, 316, 30, 77442, 24932, NB, 19

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80680430, 601, 33, 77371, 25368, RO, 154

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30741445, 504, 25, 77146, 21166, RO, 10

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780230, 504, 30, 74765, 31554, RO, 35

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50670452, 446, 99, 282234, 125982, NB, 8

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-50780219, 244, 102, 276768, 116025, NB, 31

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30780217, 487, 80, 244943, 91000, RO, 3

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-80840056, 243, 100, 237215, 96610, RO, 2

    Thailand, 38, Ladies Bag, 2016-L6, Seasonal, CK2-30270010, 546, 75, 231134, 81417, RO, 6

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80150494, 242, 5, 10833, 4129, RO, 1

    Thailand, 38, Ladies Bag, 2015-L2, Seasonal, CK2-50780074, 288, 3, 9787, 3573, RO, 56

    Thailand, 38, Ladies Bag, 2016-L4 (JIT), Seasonal, CK2-80150527, 211, 6, 9703, 3786, RO, 20

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-20741422, 86, 4, 9319, 4114, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-80741408, 211, 4, 9319, 3574, NB, 7

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670436, 159, 3, 9188, 4534, NB, 1

    Thailand, 38, Ladies Bag, 2016-L2, Seasonal, CK2-50680373, 200, 4, 8935, 3303, RO, 2

    Thailand, 38, Ladies Bag, 2016-L3, Seasonal, CK2-50670432, 74, 4, 8506, 4155, UB, 1

];

TotalNetPrice:

load SUM(TotalNetPrice) as TotalSales Resident Sales;

let vtotal=Peek('TotalSales');

trace vtotal=$(vtotal);

//Cumulative%:

//Load *, rangesum(peek(rum_sum),(TotalNetPrice/'$(vtotal)'))as run_rum Resident Sales;

//rangesum(Above(TOTAL ([Sales Value%]),0,RowNo(Total)))

//peek([Cumulative sales])+sales) as [Cumulative sales]

////let vtot=Peek('run_rum');

//trace vtot =$(vtot);

/* After computed Cumulative % of total net price i need to use it for below grade calculation */

Grade:

load *,

if(

  (TotalNetPrice/'$(vtotal)')<0.005,'A Stocks',

  if (

  (TotalNetPrice/'$(vtotal)')>0.005 and (TotalNetPrice/'$(vtotal)')<0.06,'B Stocks','C Stocks'

  )

  )

as Grade

Resident Sales;

DROP Table Sales;

[#6]

Hi Expert,

Can you please help me in calculation of cumulative values at  qlikview script level,

The data and output file are provided in excel sheet attached.

/* It's used to get the Sum of TotalNetPrice*/

TotalNetPrice:

load SUM(TotalNetPrice) as TotalSales Resident Sales;

let vtotal=Peek('TotalSales');

TRACE vtotal=$(vtotal);

/********Cumulative % based on (TotalNetPrice/SUM( TOTAL TotalNetPrice))  in load script method *************/

Cumulative%:

Load RangeSum(peek(rum_sum),(TotalNetPrice/'$(vtotal)')) as rum_sum

Resident Sales order by TotalNetPrice desc;

let vCum%=Peek('run_rum');

TRACE vCum%=$(vCum);

/*************based on Cumulative % need to derive Grade in load script method *************/

Grade:

load *,

if(

  ('$(vCum%)')<0.6,'A Stocks',

  if (

  ('$(vCum%)')>0.6 and ('$(vtotal)')<0.9,'B Stocks','C Stocks'

  )

  )

as Grade

Resident Sales;

DROP Table Sales;