topic Re: Count in QlikView

Count

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

please find my attached files.

I want the count between 9 am to 20pm.

Regards,

Mahesh.

Output:-

Super Plus	Super	Regular	Spike	Casual	Incoming	Zero	Total
937	1352	2866	3	640	716	194	6708

Re: Count

sunny_talwar — Thu, 16 Jun 2016 12:25:48 GMT

This?

Script:

Table:

LOAD Time(Frac([UPC Generate Date & Time])) as Time,

[UPC Generate Date & Time],

Category

FROM

time.xlsx

(ooxml, embedded labels, table is Sheet1);

Expression:

=Count({<Time = {"$(='>=' & Time(MakeTime(9)) & '<=' & Time(MakeTime(20)))"}>}Category)

Re: Count

sasikanth — Thu, 16 Jun 2016 12:31:52 GMT

try this exp:

=count(DISTINCT{<Hour={">=9 <=20"}>} Key_Value)

temp:

load *, RowNo() as Key_Value;

LOAD [UPC Generate Date & Time],

hour( [UPC Generate Date & Time]) as Hour,

Category

FROM

[..\Downloads_Web\time.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Count

PrashantSangle — Thu, 16 Jun 2016 12:32:10 GMT

Hi,

try like

LOAD Hour([UPC Generate Date & Time]) as hr,

Re: Count

swuehl — Thu, 16 Jun 2016 12:32:45 GMT

Or maybe like this, creating different output:

Table:

LOAD *, Hour([UPC Generate Date & Time]) as Hour;

LOAD Time(Frac([UPC Generate Date & Time])) as Time,

[UPC Generate Date & Time],

Category	Count
	6729
Casual	644
Incoming	717
Regular	2879
Spike	3
Super	1353
Super Plus	939
Zero	194

Category	Count
	6075
Casual	578
Incoming	635
Regular	2593
Spike	3
Super	1226
Super Plus	856
Zero	184

Re: Count

qlikview979 — Thu, 16 Jun 2016 12:33:43 GMT

Hi Sunny,

I Given my Expected output above.

and when i am loading this field "[UPC Generate Date & Time]" i am getting time 12:00:00 am to 11:59:59 pm .

Re: Count

qlikview979 — Thu, 16 Jun 2016 12:38:31 GMT

Hi Experts,

I got Manually working file.

Please find this

Re: Count

sunny_talwar — Thu, 16 Jun 2016 12:39:18 GMT

I added a new field in the script using the time function:

Time(Frac([UPC Generate Date & Time])) as Time,

Not sure if I can get the number you are looking for. I tried various combination of time's but it isn't working. Can you check if the numbers are right above?

Re: Count

sunny_talwar — Thu, 16 Jun 2016 12:40:45 GMT

There are 6729 numbers of rows

Try this: =Count({<Time = {"$(='>=' & Time(MakeTime(9)) & '<' & Time(MakeTime(21)))"}>}Category)

Re: Count

qlikview979 — Fri, 17 Jun 2016 05:38:36 GMT

Hi Experts,

I got the output earlier from Sasi K.please consider this I given correct answer to Sasi k, I tried with remaining people script also

It's working Good. Tanks for your Support.

Regards,

Mahesh.