Count in script

[kushalthakral]

Hi All

i have a file having data like

Model	Sales Date
a	01/01/2013
a	01/02/2013
a	01/05/2013
b	01/07/2013
b	03/05/2013
b	07/05/2013
b	01/01/2013
c	01/01/2013
c	01/03/2013
c	03/03/2013
c	01/02/2013
a	05/05/2013

Now Actually i want to calculate the count of each model in a script and then find the ratio of each model with respect to total sales

in a script

Thanks

Kushal Thakral

[SunilChauhan]

Load Count(Model) as Modelcount,

[Source Date]

from path

group by [Source Date]

hope this helps

[maxgro]

where are sales?

[kushalthakral]

Total sales means count of all dates together

Assume each as quantity 1 and add all models together as Sales

[#5]

Hi,

Lets suppose your table name is "Sales". you can load another table from this table lets say ModelCount

NocConcatenate

ModelCount:

Load Count(Model) as ModelCount,

Model

Resident Sales

Group by Model;

then in your scrip you can write expression to get ratio like =Sum (Sales) / Sum (ModelCount)

[MK_QSL]

Use something like below

Count of Sales

COUNT(Model) or COUNT([Sales Date])

% Contribution

COUNT(Model)/Count(TOTAL Model)

[maxgro]

source:
load * inline [
Model, SalesDate
a, 01/01/2013
a, 01/02/2013
a, 01/05/2013
b, 01/07/2013
b, 03/05/2013
b, 07/05/2013
b, 01/01/2013
c, 01/01/2013
c, 01/03/2013
c, 03/03/2013
c, 01/02/2013
a, 05/05/2013
];

left join (source)
LOAD
Model, count(SalesDate) as cnt
Resident source
group by Model;

left join (source)
LOAD
count(SalesDate) as cnttotal
Resident source
;

table:
NoConcatenate load
Model, SalesDate, 
1 / cnt as pct,
1 / cnttotal as pcttotal
Resident
source;

DROP Table source;

[Gysbert_Wassenaar]

See attached example.

[SunilChauhan]

in myscript use [Source Date] in count and model outside

load

count([Source Date]) as Totalcount,

Model

from path

group by Model;

[its_anandrjs · Accepted solution]

Load your table like below

Table:

LOAD * INLINE [

    Model,Sales Date

    a, 01/01/2013

    a, 01/02/2013

    a, 01/05/2013

    b, 01/07/2013

    b, 03/05/2013

    b, 07/05/2013

    b, 01/01/2013

    c, 01/01/2013

    c, 01/03/2013

    c, 03/03/2013

    c, 01/02/2013

    a, 05/05/2013];

Left Join

LOAD

Model,

Count([Sales Date]) as ModelCount

Resident Table

Group By Model;

Left join

LOAD 

Count([Sales Date]) as CntTotal 

Resident Table; 

Left Join

LOAD

Model,

Num((sum(ModelCount) / sum(CntTotal)),'#.#0%') as Ratio

Resident Table

Group By Model;