Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

grade in load script?

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

5 Replies
vcanale
Partner
Partner

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
Master II
Master II

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

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;

Not applicable
Author

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;

Not applicable
Author

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;