Optimizing IF-statements for setting flags

[#1]

Hi everyone

I'm currently using a lot of IF-statements to set up flags for categorizing delivery time.

//Short example:

IF(DeliveredTime=-3, 1, null()) AS D_3_Before,

IF(DeliveredTime=-2, 1, null()) AS D_2_Before,

IF(DeliveredTime=-1, 1, null()) AS D_1_Before,

IF(DeliveredTime=1, 1, null()) AS D_1_After,

IF(DeliveredTime=2, 1, null()) AS D_2_After,

IF(DeliveredTime=3, 1, null()) AS D_3_After

Since only one of these flags will be 1 and the rest will always be null(), there must be a way to optimize the run time considerably?


Regards
Frederik

[Miguel_Angel_Baeyens]

Hello Frederik,

Using ApplyMap and Maping Loads may help you to do that. Given

DeliverFlagMap:LOAD * INLINE [ DeliveredTime, Flag -3, 1 -2, 1 -1, 1 1, 1 2, 1 3, 1];

you can use then in your LOAD statement as a new field

ApplyMap('DeliverFlagMap', DeliveredTime) AS FlagField

Now you don't need 6 fields, but one.

Another option is the use of Match like

If(Match(DeliveredTime, '-3', '-2', '-1', '1', '2', '3') > 0, 1) AS FlagField

Depending on what analyses you want to perform, you may want to use the above.

[#3]

Hi Miguel, thank you for your reply.

As I understand it, with one ApplyMap function I can set one predefined variable. But it is not possible to have a single ApplyMap function to point out the appropriate variable (D_3_Before, D_2_Before etc.) and set it to "1" (while the remaining 5 variables are set to null()).

The ApplyMap you describe, is then useful for evaluating whether DeliveredTime is any of the chosen values (-3, -2, -1, 1, 2 or 3) but it will not store which the values it was.

Match() seems to have the same limit.

So I don't think I in this situation can Map or Match my way to a more efficient processing?