Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Recalculate field in script

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?

1 Solution

Accepted Solutions
Employee
Employee

Re: Recalculate field in script

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

3 Replies
MVP
MVP

Re: Recalculate field in script

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

Employee
Employee

Re: Recalculate field in script

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

MVP
MVP

Re: Recalculate field in script

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

Community Browser