topic Re: Dynamic Pick and Match in QlikView

Dynamic Pick and Match

Anonymous — Mon, 18 Jun 2018 17:20:16 GMT

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!

Re: Dynamic Pick and Match

vishsaggi — Mon, 18 Jun 2018 19:03:31 GMT

What do these variables hold?

Re: Dynamic Pick and Match

Anonymous — Mon, 18 Jun 2018 19:37:09 GMT

These variables hold formulas to calculate sales forecast

Re: Dynamic Pick and Match

MarcoWedel — Mon, 18 Jun 2018 20:08:01 GMT

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

Re: Dynamic Pick and Match

vishsaggi — Mon, 18 Jun 2018 20:20:58 GMT

So you still have to create variables right?

Re: Dynamic Pick and Match

marcus_sommer — Tue, 19 Jun 2018 12:24:11 GMT

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

Re: Dynamic Pick and Match

Anonymous — Wed, 20 Jun 2018 21:27:01 GMT

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.

Re: Dynamic Pick and Match

Anonymous — Wed, 20 Jun 2018 21:30:25 GMT

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)

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.

Re: Dynamic Pick and Match

Anonymous — Wed, 20 Jun 2018 21:31:25 GMT

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

Re: Dynamic Pick and Match

Anonymous — Wed, 20 Jun 2018 21:35:00 GMT

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.

Re: Dynamic Pick and Match

marcus_sommer — Thu, 21 Jun 2018 07:06:30 GMT

I think you don't want to hear the following but suggest to rethink the whole approach.

Your variables seems to be rather unique so that it makes not much sense to put these contents within variables. Further the content isn't really dynamic - nearly all values within the conditions and the returns are hard-coded and I think it are far too many nested branches as that it is sensible to develop such huge constructs - from a maintainability point of view it looked rather like a nightmare ...

I think I would go in the direction of creating one or several matrix-tables (maybe filled from an Excel source) which are associated or merged (joined and/or mapped) to your table to simplify the matching of the right forecast-factors.

- Marcus