Formulae: Subset functions

Valentina · April 23, 2014 7:41AM

Hi

I am trying to use subset functions to do the following but it seems that more functions are needed.

I have a column with ID's and another column showing the steps of the ID. The Temp column shows the temperature of the ID at each step. Ideally I want to create a fourth column (like First step temp below) showing the temperature of the First Step only, across all same ID cells.

Hope it makes sense. Any ideas are welcomed. Thanks

ID Steps Temp First step temp
A First Warm Warm
A Middle Cool Warm
A Last Warm Warm
B First Warm Warm
B Last Warm Warm
C First Cool Cool
C Middle Cool Cool
C Middle Warm Cool
C Last Cool Cool

Bart · April 23, 2014 7:49AM

Hi Valentina,

Please have a look into the file attached. Hopefully it is what you wanted.

Regards,
Bart

paola · April 23, 2014 7:57AM

You also might wish to consider using either of these two formulas in Field organiser/DataManager or Data Explorer space:

SUBSET_FIRSTNONNULL([Temp], SUBSET([Id]))
Taking the first value in the [Temp] field and repeating it for each record with same [Id]. Works fine if the "First" is always the first record for each [Id]. In case it is not, you might use:

SUBSET_FIRSTNONNULL([Temp], SUBSET2([Id],[Steps],[Id],"First"))
Same logic, but making sure the [Temp] value taken is the one where [Steps] ="First".

Valentina · April 23, 2014 8:51AM

Thanks a lot guys!! All formulas worked perfect.

Welcome!

Categories

Ideas Parade

Tagged