Formula in DB tool

[Mansenie]

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? 

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

[benakesh · Accepted solution]

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 . 

[Mansenie]

I did as you said and it works. Now i join all my rows and i am getting a lot of nulls because there are so many rows. any idea how to make all my answers fit in one row under the headers?

[benakesh · Accepted solution]

Hi @Mansenie ,

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