Dynamic Pick and Match

[Anonymous]

Hi!

We have this code:

Pick(Match(IDArbol, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28)

LOAD *,

     Pick(Match(IDArbol, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28),

$(vVARIABLE_1),

$(vVARIABLE_2),

$(vVARIABLE_3),

$(vVARIABLE_4),

$(vVARIABLE_5),

$(vVARIABLE_6),

$(vVARIABLE_7),

$(vVARIABLE_8),

$(vVARIABLE_2),

$(vVARIABLE_9),

$(vVARIABLE_10),

$(vVARIABLE_1),

$(vVARIABLE_12),

....

          $(vVARIABLE_28)

Unfortunately the number of IDArbol could go up from 28, is there any way we could do a dynamic Pick and Match to solve this or another function which could work similar to pick and match?

Thank you!

[vishsaggi]

What do these variables hold?

[Anonymous]

These variables hold formulas to calculate sales forecast

[MarcoWedel]

First thing that comes to mind is that Match(x,1,2,3,4,5) will return x (where defined), so your expression should also work when shortened to

Pick(IDArbol,

        $(vVARIABLE_1),

        $(vVARIABLE_2),

        $(vVARIABLE_3),

        $(vVARIABLE_4),

        $(vVARIABLE_5),

        $(vVARIABLE_6),

        $(vVARIABLE_7),

        $(vVARIABLE_8),

        $(vVARIABLE_2),

        $(vVARIABLE_9),

        $(vVARIABLE_10),

        $(vVARIABLE_1),

        $(vVARIABLE_12),

        ...)

Do you have an example e.g. of your variable  vVARIABLE_1 in comparison to vVARIABLE_2?

hope this helps

regards

Marco

[vishsaggi]

So you still have to create variables right?

[marcus_sommer]

If your variables respectively the forecast-calculations behind them are not too different it could be an alternatively to use a parametrized variable to simplify the task. This means (simplified) something like this:

set var = sum(Sales) * evaluate(Faktor$1);

load *, $(var(IDArbol)) as Forecast from Source;

More background to this feature could you find here:

Variable That Acts Like a User-Defined Function.

Dynamic Field Expressions with Variables

Variables with Parameters

- Marcus

[Anonymous]

These are examples of the variables:

vVARIABLE_1: PromImporte_010 * IF(_FlagPrevioPuente = 1, 1.88, 0.84) * IF(_FlagPrevioPuente <> 1 , IF(_FlagVacaciones = 1, 1.44, 0.92), 1)

vVARIABLE_2: PromImporte_010 * IF(_FlagVacaciones = 1, 1.39, 0.85) * IF(_FlagVacaciones = 1 , IF(_FlagPrevioPuente = 1, 1.44, 0.96), 1) * IF(_FlagVacaciones <> 1 , IF(IDTipoClima = 5, 1.59, 1), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5 , IF(IDTipoClima = 4, 1.12, 1), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4 , IF(IDTipoClima = 3, 1.12, 1), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4  AND IDTipoClima <> 3 , IF(IDTipoClima = 2, 0.85, 1), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4  AND IDTipoClima <> 3  AND IDTipoClima <> 2 , IF(IDTipoClima = 1, 0.85, 1), 1) * IF(_FlagVacaciones = 1  AND _FlagPrevioPuente <> 1 , IF(IDTipoPrecipitacionPluvial = 1, 0.7, 1.05), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4  AND IDTipoClima <> 3  AND IDTipoClima = 2 , IF(IDTipoPrecipitacionPluvial = 1, 0.71, 1.04), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4  AND IDTipoClima <> 3  AND IDTipoClima <> 2  AND IDTipoClima = 1 , IF(IDTipoPrecipitacionPluvial = 1, 0.71, 1.04), 1)

The code we actually have solves the formula and stores the value in the record.

[Anonymous]

Hi

Unfortunately the client wants the variables to hold the formulas for the forecast, and these must be assembled dynamically, these are some examples of these formulas:

vVARIABLE_1: PromImporte_010 * IF(_FlagPrevioPuente = 1, 1.88, 0.84) * IF(_FlagPrevioPuente <> 1 , IF(_FlagVacaciones = 1, 1.44, 0.92), 1)

vVARIABLE_2: PromImporte_010 * IF(_FlagVacaciones = 1, 1.39, 0.85) * IF(_FlagVacaciones = 1 , IF(_FlagPrevioPuente = 1, 1.44, 0.96), 1) * IF(_FlagVacaciones <> 1 , IF(IDTipoClima = 5, 1.59, 1), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5 , IF(IDTipoClima = 4, 1.12, 1), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4 , IF(IDTipoClima = 3, 1.12, 1), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4  AND IDTipoClima <> 3 , IF(IDTipoClima = 2, 0.85, 1), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4  AND IDTipoClima <> 3  AND IDTipoClima <> 2 , IF(IDTipoClima = 1, 0.85, 1), 1) * IF(_FlagVacaciones = 1  AND _FlagPrevioPuente <> 1 , IF(IDTipoPrecipitacionPluvial = 1, 0.7, 1.05), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4  AND IDTipoClima <> 3  AND IDTipoClima = 2 , IF(IDTipoPrecipitacionPluvial = 1, 0.71, 1.04), 1) * IF(_FlagVacaciones <> 1  AND IDTipoClima <> 5  AND IDTipoClima <> 4  AND IDTipoClima <> 3  AND IDTipoClima <> 2  AND IDTipoClima = 1 , IF(IDTipoPrecipitacionPluvial = 1, 0.71, 1.04), 1)

The current code gets the correct formula and solves it. Until now we have managed to bring the correct formula but haven't been able to get it to solve it.

[Anonymous]

The current code gets the correct value and solves the formula, at least in this qvw we don't need any more variables.

[Anonymous]

Until now, we are able to get the right formula, but we haven't been able to get qlikview to solve it with this code:

Analitica_Transacciones_Hechos_$(vSlice):

LOAD *,

      SubField('$(vVARIABLE_Arr)', '|', IDArbol) AS ProyImporte_010;

LOAD %CubeID,

     %CubeDescription,

ApplyMap('Map_AsignaArbol', (UPPER(Cliente.Formato) & '||' & UPPER(DiaSemanaTicket)), ApplyMap('Map_AsignaArbol', ('OTROS||' & UPPER(DiaSemanaTicket)), 'NULL')) AS IDArbol,

%Key_Promedio,

     %IDFecha,

     %Fecha,

     DiaSemanaTicket,

     HoraTicket,

     _FlagVacaciones,

_FlagPrevioPuente,

_FlagSemanaQuincena,

IDTipoClima,

IDTipoPrecipitacionPluvial,

     %Cliente_SCD,

     Cliente.Formato,

     IDEstacion,

     AAImporte_004,

     AAImporte_005,

     AAPiezas_005,

     AAImporte_010,

     Importe_004,

     Importe_005,

     Piezas_005,

     Importe_010,

     CantTicketTotal,

     CantTarjetaDescuento,

     CantTicket_Promocion,

     CantDescuentoPromocion,

PromedioMeses,

PromImporte_010,

     PromImporte_004,

     PromImporte_005,

     PromPiezas_005

Resident Analitica_Transacciones_Hechos_Tmp2_$(vSlice);

The vVARIABLE_Arr holds all the formulas we have calculated so far while the IDArbol tells us which one we need.

We also added the evaluate function like this:

evaluate(SubField('$(vVARIABLE_Arr)', '|', IDArbol)) AS ProyImporte_010;

But it only returns a null value.