Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
lmj0
Contributor
Contributor

Count all values beginning with S

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 

Labels (1)
2 Solutions

Accepted Solutions
Or
MVP
MVP

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

View solution in original post

BrunPierre
Partner - Master
Partner - Master

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

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

View solution in original post

3 Replies
Or
MVP
MVP

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
Partner - Master
Partner - Master

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

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

lmj0
Contributor
Contributor
Author

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;