topic Count all values beginning with S in QlikView

Count all values beginning with S

lmj0 — Tue, 04 Apr 2023 13:16:39 GMT

load

Date

Count(Distinct "storage") as "S storage "

Resident xxxx

Where left(Storage,1)= 'S'

Group by date

This is my script but when I load I get 26 rows instead of 73 rows

I aim to count values begining with the letter S in column Storage

Re: Count all values beginning with S

Or — Tue, 04 Apr 2023 13:41:06 GMT

This code looks fine. Do note that it will only return rows for dates that actually have at least one S storage, since you've filtered out all other rows. If you'd like to keep all dates (including those with no S storage) you should avoid using a Where condition and instead use a conditional count, such as Count(distinct If(Left(Storage,1)='S',Storage).

Re: Count all values beginning with S

BrunPierre — Tue, 04 Apr 2023 14:14:22 GMT

Just to be sure you account for any mixed case of the first letter S, do this.

Where Left(Capitalize(Storage),1)= 'S'

Re: Count all values beginning with S

lmj0 — Tue, 04 Apr 2023 16:48:33 GMT

WHData:

LOAD

"Warehouse No.",

Quant,

Plant,

Batch,

"Storage Type",

"Storage Bin",

"Last movement",

"Time",

"GR Number",

"Storage Unit",

"Stor. Loc."

FROM xxx

Warehouse:

Load

"Last movement",

Count(DISTINCT "Storage Bin") as Count_S_bins,

192 AS MaxCapacity_P4

Resident WHData

Where Left("Storage Bin",1)='S'

Group by "Last movement", 192 ;

Outer Join (Warehouse)

Load

"Last movement",

Count( DISTINCT "Storage Bin") AS OtherBins,

272 as MaxCapacity_T1

Resident WHData

Where not Match(Left("Storage Bin",1),'S','R')

Group by "Last movement", 272;