Formula in DB tool

i need to load data into a table and use that information to create new columns. For example, I am adding to a table the name of products a customer orders, the date of the order, the invoice and timestamp. I need to create a report where i have several calculated columns such as : last 4 week average for a product, last 7 days last year avg etc.... I wanted to use the in db formula tool to write something similar to this sql query :

SELECT product_type,Count(product)FROM table
WHERE order_dt <= Current_Date -1 AND order_dt>= current_date-28
GROUP BY product_type

can anyone help me do this?

In Database

In-Database

Accepted answers

benakesh

Hi @Mansenie ,

SELECT product_type,Count(product)FROM table
WHERE order_dt <= Current_Date -1 AND order_dt>= current_date-28
GROUP BY product_type

If you are trying to group/summarize you need indb summarize tool .

For 'where clause ' you use indb filter before summarize tool .

benakesh

Hi @Mansenie ,

Summarize tool with group by first column( car_type) and max of all other columns will put data in one row.

All comments

MarqueeCrew

@Mansenie ,

That's a job for an input tool. Or you can use an input tool and follow it with a filter and then a summarize tool. You can either embed the SQL or use those In-DB tool that I mentioned to create the calculation. When you use the In-DB tools, you won't actually be selecting all of the data, but rather the workflow will construct the SQL behind the scenes to get to your result.

Cheers,

Mark

Mansenie

i understand that but the problem is that i am creating a user db table to enter the details that i want and from that i want to make those calculations. i dont want to bring the data ou (data stream out) because it is a lot of data.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235