Recalculate field in script

[#1]

At the end of my script I want to recalculate the [Exp hrs].

In one LOAD-statement I'm adding the fields [%Date], [Actual Hours], [Exp hrs] and [Calculation Method] to [MyTable]

I'm using a JOIN to add the field [Target Prod]

Now depending on the Calculation Method the Expected hours need to be recalculated.

My join doesn't recalculate all the records in [MyTable]

// use calculation method to recalculate [Exp hrs]

LEFT JOIN ([MyTable])

LOAD If ([Calculation Method]=2,

      IF ([Actual Hours]>0, [Target Prod],

      0),

      [Exp hrs])

      as [Exp Hours]

RESIDENT MyTable;

Drop Fields [Exp hrs];

Can anyone tell me how I'm supposed to recalculate all records?

[rbecher]

Hi Erik,

I think you'll need at least one identifying column to match the row for the join:

LEFT JOIN (MyTable)

LOAD ID, <Expression> as Exp

Resident MyTable;

- Ralf

[Clever_Anjos · Accepted solution]

Why JOIN?

You can

NewTable:

LOAD

  *,

If ([Calculation Method]=2,

      IF ([Actual Hours]>0, [Target Prod],

      0),

      [Exp hrs])

      as [Exp Hours]

resident MyTable;

Drop table MyTable

[rbecher]

Why Join: if this is a large fact table it could have serious memory implications..