topic Re: Add a Calculated Column through Load Script in Connectivity & Data Prep

Add a Calculated Column through Load Script

jon-laudner — Thu, 02 May 2024 18:16:29 GMT

I need to add a column to a table, based on data from within that table itself.

Let's use this example, Table Name [Invoice Details]:

Invoice Number	Item Cost	Item Sale Price
123456	$5.00	$10.00
987654	$7.50	$20.00

I need to add a new column to the [Invoice Details] table that is [Item Profit] that will be [Item Sale Price] - [Item Cost].

Now, I know I could do this when I initially load the table, but in my example Item Cost and Item Sale Price are both pretty complex calculations to begin with, and I don't want to copy both of those expressions into yet another field to complete the [Item Profit] calculation. Said another way, if in the future, the [Item Sale Price] calculation changes, I would like that change to "cascade" to the [Item Profit] calculation without needing the developer to remember to copy his changes to both fields.

So, how do I essentially LEFT JOIN a table to itself?

Essentially, I think I need to do this:

LEFT JOIN ([Invoice Details])
LOAD [Item Sale Price] - [Item Cost] as [Item Profit]
Resident [Invoice Details]

But that obviously doesn't work out for me. What am I missing here?

Re: Add a Calculated Column through Load Script

BrunPierre — Thu, 02 May 2024 18:54:33 GMT

This is how to go about it.

Left Join(Invoice.Details)
LOAD Invoice.Number as Invoice.Number ,
Sum(Item.Cost - Item.Sale.Price) as Item.Profit

Resident Invoice.Details
Group By Invoice.Number;

Re: Add a Calculated Column through Load Script

jon-laudner — Thu, 02 May 2024 19:08:23 GMT

In this case, there's no GROUP BY necessary, it's line-by-line. And when I try to LEFT JOIN a table to itself, I get an out of memory exception.

Re: Add a Calculated Column through Load Script

BrunPierre — Thu, 02 May 2024 19:24:09 GMT

That line of code groups the data by the Invoice.Number field. It ensures that the calculated profit is aggregated for each unique invoice number.

Share the script to assist with debugging.

Re: Add a Calculated Column through Load Script

jon-laudner — Thu, 02 May 2024 19:37:48 GMT

That definitely worked. I tried but without my "Primary Key" or Invoice.Number in this example, and I think that's why I got the out of memory exception.

In my case, it was a bit different than the original question, but this is the code that ended up working:

LEFT JOIN ([Order Details])
LOAD OrderLineID as [OrderLineID], Only(Upper([Division ID])) as SA_DIVISIONID, Only(Upper([Is Invoiced])) as [SA_ISINVOICED]
RESIDENT [Order Details]
GROUP BY [OrderLineID]

So I was successfully able to LEFT JOIN a table to itself. The reason I'm doing it this way is I have a Data Model app that has the entire load script, while this code is going in just one of the many UI apps to do some Section Access, and I didn't want to add these SA_ columns to the original Data Model.

Thank you very much for your help!