Canvas count the numbers from a table

[Vladpov]

Hi I'm new with canvas and so far I created two tables where I have id numbers in separate rows.

I try to make a count of num1 occurrence so instead of 7 ones there would be one row with number 7 or number 4 if we'd speak about another table.

Any help is appreciated!!

[tims (Tim Schnell)]

Hey @Vladpov, normally you could accomplish something like this with a subquery in regular SQL, unfortunately subquery support in essql is limited. Is this perhaps a value that you could calculate and store during ingest or using a scripted field?

[Vladpov]

Hey @tims, thank you for you reply! After a few attempts to achieve it we decided to try manage it as you say.

Can you please give me any hints about options that exist and are able to filter the messages during ingest?

The thing is that we have logs coming constantly with id number and suffix (dn and mt) and we need to get somewhere either during ingest or after some script that could calculate number of successfully delivered messages.

If message was delivered successfully depends on the time id number recieved second suffix 'dn' so message has both suffixes 'mt' and 'dn' that should be within one hour.

Thank you very much for any help!