Count all values beginning with S

[lmj0]

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 

[Or · Accepted solution]

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

[BrunPierre · Accepted solution]

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

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

[lmj0]

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;