Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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).
Just to be sure you account for any mixed case of the first letter S, do this.
Where Left(Capitalize(Storage),1)= 'S'
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).
Just to be sure you account for any mixed case of the first letter S, do this.
Where Left(Capitalize(Storage),1)= 'S'