Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
olguita2014
Creator
Creator

transform formula to script

Hi,

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

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

 

 

Labels (1)
5 Replies
vishsaggi
Champion III
Champion III

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;

 

Anil_Babu_Samineni

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner - Champion III
Partner - Champion III

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;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
olguita2014
Creator
Creator
Author

Thanks,

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

 

VEHCHASISResultMVESUFIJO=Aggr(Sum(Cantidad_Saldo),VEHCHASIS)
JTMZD9EV6E50149700AA1
JTMZF9EV1ED0220400AA1
JTDKD3B35F15678801AA1
5YFBU9HE7FP1421041AA1
4T1BB3EK0AU1186661AA1

 

The actual values are those of the last column

olguita2014
Creator
Creator
Author

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.