topic Re: Count in script in QlikView

Count in script

kushalthakral — Thu, 01 May 2014 10:17:31 GMT

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

Re: Count in script

SunilChauhan — Thu, 01 May 2014 10:20:23 GMT

Load Count(Model) as Modelcount,

[Source Date]

from path

group by [Source Date]

hope this helps

Re: Count in script

maxgro — Thu, 01 May 2014 10:21:26 GMT

where are sales?

Re: Count in script

kushalthakral — Thu, 01 May 2014 10:27:20 GMT

Total sales means count of all dates together

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

Re: Count in script

Thu, 01 May 2014 10:28:20 GMT

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)

Re: Count in script

MK_QSL — Thu, 01 May 2014 10:32:22 GMT

Use something like below

Count of Sales

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

% Contribution

COUNT(Model)/Count(TOTAL Model)

Re: Re: Count in script

maxgro — Thu, 01 May 2014 10:32:48 GMT

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;

Re: Count in script

Gysbert_Wassenaar — Thu, 01 May 2014 10:32:57 GMT

See attached example.

Re: Count in script

SunilChauhan — Thu, 01 May 2014 10:35:03 GMT

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

load

count([Source Date]) as Totalcount,

Model

from path

group by Model;

Re: Count in script

its_anandrjs — Thu, 01 May 2014 11:02:12 GMT

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;

Re: Count in script

kushalthakral — Thu, 01 May 2014 17:54:20 GMT

Thank you All

Regards

kushal Thakral