topic Re: Recalculate field in script in QlikView

Recalculate field in script

Mon, 23 Sep 2013 08:47:06 GMT

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 — Mon, 23 Sep 2013 20:39:08 GMT

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 — Mon, 23 Sep 2013 21:11:27 GMT

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 — Tue, 24 Sep 2013 15:17:10 GMT

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