count labels by quantity

[#1]

I need a script to take

QTY of 1 - 29 count = 0

qty of 30 -59 count =1

qty of 60 89 count = 2

qty of 90 - 119 count =3

The file attached is a sip it is an example of the data I am using.

The correct answer is 220 labels 

[Frank_Hartmann]

try that in script:

LOAD

    Qty,

    If(Qty<30,0,if(Qty<60,1,if(Qty<90,2,if(Qty<120,3,4)))) as Rating

FROM [lib://desktop/attachFile.xlsx]

(ooxml, embedded labels, table is Sheet1);

and then  sum(Rating) as Expression in Textboxobject

[#3]

its reading it as TEXT how do I format to a number.  then its in the bag.

Thank you

[pathiqvd]

Hi,

Try like this,

if(Qty>0 and Qty<=29,Dual('0-29',1),

if(Qty>=30 and Qty<=59,Dual('30-59',2),

if(Qty>=60 and Qty<=89,Dual('60-89',3),

if(Qty>=90 and Qty<=119,Dual('90-119',4)

)))) as qtybucket

Here Dual will convert to Number Format, Now can sort

Regards,

[sunny_talwar]

Reading it as TEXT? What does that mean? I mean I see an expression (Sum(Rating)) and you cannot Sum a text field... It is a number field....

[#6]

its reading as text when I export to excel.