Howdy,
I am used to doing a calculation in the old Briquery like the following.
In this example, the text fields can be summed
and then later the duplicates can be removed. What is left is one record for Stock_Code and one Manufacturers and PN’s.
I would like to do a similar process In Toad Data Point. Unfortunately, the LISTAGG information is very difficult to understand.
I am not sure if I should do that in the initial query, adding a new column or in the transform/cleanse section.
Here are the same records in TDP version 6.2.
I anyone could steer me in the right direction, I would certainly appreciate it as this is a necessary process.
Thanks,
jkruegertx
TDP doesn't have ListAgg() functions in its library for all database platforms, so it's probably best to use the database's specific ListAgg() or StringAgg() functions in the original query itself.
You didn't mention which database you're doing this on, but the snap below shows a simple example using the ListAgg() function for an Oracle table. I used a table I already had vs. setting up a new table like yours, but it's easy to see that
- My "regnum" column relates to your "StockCode" column,
- My "regstate" column relates to your "mfrpn" column, and
- My "state_stacked" column relates to your desired output column "Manufacturers andPN's" column
Note that I used commas (vs. spaces between) to separate the different values in the stacked output, and it's probably important that you sort your listagg'd output by the column that you're aggregating ("regstate" in my case... "mfrpn" in your case)... otherwise, you may not find duplicated aggregations even when the list contains the exact same values.
Hope this helps.
I personally find Toad Workbook easier for re-creating the Brio\Hyperion queries, but basically same as what Gary suggested.
This example is SQL specific, but create a calculated field for the field you want agg'd together:
Then add a 'Group By:
Final Results:
