Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Count column value totals.

LeeC1989
Level 1
Level 1

Hi everyone,

This is my first post, so be gentle!

I'm also new to campaign, so be even gentler!

I am looking to find the most efficient way of calculating column counts of values. The challenge is making is fairly fluid.

The schema that I have, has (as of today), 69 columns. But this can vary on week to week. I'm looking at getting the column count and then sending an alert to someone if the count is 0...this must also count NULL values.

For example -

NameAgeShoe Size
John
Lee45
Elvis21
Greg
James54
Steve27

So this would give me counts of Name - 6, Age - 4 and Shoe size =  0.

This would then Alert to a user saying shoe size is empty!

I don't need to store the counts anywhere permanently.

Thanks to all!

Lee

1 Accepted Solution
Amit_Kumar
Correct answer by
Community Advisor
Community Advisor

Hi Lee,

You need to build a query and in additional data use something like below

count(Iif((@Name=''), NULL, @id))

count(Iif((@age=''), NULL, @id))

count(Iif((@shoeSize=''), NULL, @id))

This should give you the exact count.

Regards,

Amit

View solution in original post

2 Replies
Amit_Kumar
Correct answer by
Community Advisor
Community Advisor

Hi Lee,

You need to build a query and in additional data use something like below

count(Iif((@Name=''), NULL, @id))

count(Iif((@age=''), NULL, @id))

count(Iif((@shoeSize=''), NULL, @id))

This should give you the exact count.

Regards,

Amit

View solution in original post

Jonathon_wodnicki
Community Advisor
Community Advisor

Hi,

This can be generalized by iterating over the schema's cols to build a query def (or raw sql), then similarly iterating over the cols in the result.

Thanks,

-Jon