Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikview979
Specialist
Specialist

Count

Hi Experts,

please find my attached files.

I want the count between 9 am to 20pm.

Regards,

Mahesh.

Output:-

   

Super PlusSuperRegularSpikeCasualIncomingZeroTotal
9371352286636407161946708
1 Solution

Accepted Solutions
sasikanth
Master
Master

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);

View solution in original post

9 Replies
sunny_talwar

This?

Capture.PNG

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)

sasikanth
Master
Master

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);

PrashantSangle

Hi,

try like

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

     Category

FROM

time.xlsx

(ooxml, embedded labels, table is Sheet1);

In straight table

dimension:  Category

expression : count({<hr={">=9<=20"}>}Category)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
swuehl
MVP
MVP

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

FROM

time.xlsx

(ooxml, embedded labels, table is Sheet1);

=Count({<Hour = {">=9<=20"}>}Category)

Category Count
6729
Casual644
Incoming717
Regular2879
Spike3
Super1353
Super Plus939
Zero194

=Count({<Hour = {">=9<20"}>}Category)

Category Count
6075
Casual578
Incoming635
Regular2593
Spike3
Super1226
Super Plus856
Zero184
qlikview979
Specialist
Specialist
Author

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 .

qlikview979
Specialist
Specialist
Author

Hi Experts,

I got Manually working file.

Please find this

sunny_talwar

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?

sunny_talwar

There are 6729 numbers of rows

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


Capture.PNG

qlikview979
Specialist
Specialist
Author

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.