Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hello Frederik,
Using ApplyMap and Maping Loads may help you to do that. Given
you can use then in your LOAD statement as a new fieldDeliverFlagMap:LOAD * INLINE [ DeliveredTime, Flag -3, 1 -2, 1 -1, 1 1, 1 2, 1 3, 1];
Now you don't need 6 fields, but one.ApplyMap('DeliverFlagMap', DeliveredTime) AS FlagField
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.
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?