Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
What do these variables hold?
These variables hold formulas to calculate sales forecast
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
So you still have to create variables right?
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
- Marcus
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.
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.
The current code gets the correct value and solves the formula, at least in this qvw we don't need any more variables.
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.