topic Re: transform formula to script in App Development

transform formula to script

olguita2014 — Sat, 16 Feb 2019 18:09:25 GMT

Hi,

Help me please!!!!..... I want to put this expression into the script.

Sum({<KARFECHA={"<=$('$(=MAX(KARFECHA))')"}>} Cantidad_Saldo)

Re: transform formula to script

vishsaggi — Sat, 16 Feb 2019 18:24:01 GMT

May be try below not sure if this is works, if not can you send some sample data with and example output.

LOAD *
FROM Tablename;

NoConcatenate
MaxLoad:
LOAD DimKeyField,
IF(KARFECHA <= Max(KARFECHA), Cantidad_Saldo) AS CantidFlow_Flag
Resident YouractualTable
Group By DimKeyField;

LEFT JOIN(MaxLoad)

LOAD DimKeyField,

Sum(CantidFlow_Flag) AS SumField

Resident MaxLoad

Group By DimKeyField;

Left Join(YouractualTable)

LOAD *

resident MaxLoad;

Drop Table MaxLoad;

Re: transform formula to script

Anil_Babu_Samineni — Mon, 18 Feb 2019 04:06:15 GMT

Qlik preferred to use in Design level logic, If too luck complexity involved !! What is the purpose, you are going to back to the script Instead in Design?

Re: transform formula to script

jonathandienst — Mon, 18 Feb 2019 05:27:56 GMT

A front end expression is evaluated in some context - for example, a text object (no dimensions), a table (with some set of dimensions), and so on. To evaluate this in script, you would need to understand this context.

You can use a pattern like this:

T_Max: 
LOAD MAX(KARFECHA) as maxFecha
Resident ...

Let vMaxFecha = Peek('maxFecha');

...

Fact2:
LOAD ...
	Sum(If(KARFECHA <= $(vMaxFecha), Cantidad_Saldo)) as Result,
	...
Resident ...
Group by ...
	
...
	
Set vMaxFecha =;	
DROP Table T_Max;

Re: transform formula to script

olguita2014 — Mon, 18 Feb 2019 22:11:50 GMT

Thanks,

I have a problem, the sum is not performing correctly for all dates:

VEHCHASIS	Result	MVESUFIJO	=Aggr(Sum(Cantidad_Saldo),VEHCHASIS)
JTMZD9EV6E5014970	0	AA	1
JTMZF9EV1ED022040	0	AA	1
JTDKD3B35F1567880	1	AA	1
5YFBU9HE7FP142104	1	AA	1
4T1BB3EK0AU118666	1	AA	1

The actual values are those of the last column

Re: transform formula to script

olguita2014 — Mon, 18 Feb 2019 23:11:22 GMT

Hi,
My script is and the results are ok:
VEH_KARDEX:
LOAD
Clave_CompraDetalle_Kardex_Vehiculo,
Numero_Fecha_Kardex,
KarAño,
KarMes,
KarDia,
Cantidad_Saldo,
Clave_Agencia_Kardex,
Codigo_Agencia,
Fecha_Fin_Kardex,
KARFECHA,
KARHORA,
KARVALORVEHI,
KARAGECODIDEST,
KARSIGNO,
KARNUMEROMOVIMIENTO,
TITCODIGO,
KARESTADO,
KARUSUARIOCREA,
KARFECHACREA,
KARUSUARIOMODIFICA,
KARFECHAMODIFICA,
LIVCODIGO
FROM [lib://QVDs/VEH_KARDEX_TMP.qvd]
(qvd)
Where NOT IsNull(KARFECHA);

NoConcatenate
Rango_Fechas_Kardex_Tmp:
Load Distinct
'31/12/2005' as Fecha_Inicio_Kardex,Fecha_Fin_Kardex as Fecha_Kardex
Resident VEH_KARDEX;
Inner Join IntervalMatch (Fecha_Fin_Kardex)
Load Distinct Fecha_Inicio_Kardex,Fecha_Kardex
Resident Rango_Fechas_Kardex_Tmp;

Fact2:
Load
Clave_CompraDetalle_Kardex_Vehiculo,
Fecha_Fin_Kardex,
Cantidad_Saldo
Resident VEH_KARDEX;
Join
Load Fecha_Fin_Kardex,
Fecha_Kardex
Resident Rango_Fechas_Kardex_Tmp;

NoConcatenate
Fact3:
LOAD
Clave_CompraDetalle_Kardex_Vehiculo,
Sum(Cantidad_Saldo) as Result
Resident Fact2
Group by Clave_CompraDetalle_Kardex_Vehiculo, Fecha_Kardex;
Drop Table Fact2;

But the time of performance of Qlik is bad.