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: 
ecolomer
Master II
Master II

Transform and Generate a new table in script

I have an table with the position of the accounts (POS) to a certain date

Cuenta - Fecha - Posición

and another with the movements each day (MOV)

Cuenta - Fecha - Importe

I want to get a table with the following data:

Cuenta - Fecha - Entradas - Salidas - Posición

with one record for each date.


Where "Entradas" = sum(Importe if >0) and "Salidas" = sum(Importe if <0)

Thank's for your help

2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Is this what you are after?

Temp:

LOAD Cuenta,

  Fecha,

  Posición

FROM ... ;

Join(Temp)

LOAD Cuenta,

  Fecha,

  Importe

FROM ... ;

Final:

LOAD Cuenta,

  Fecha,

  Posición,

  Sum(RangeMax(Importe, 0)) as Entradas,

  Sum(RangeMin(Importe, 0)) as Salidas

Resident Temp

Group By Cuenta,

  Fecha,

  Posición;

DROP TABLE Temp;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ecolomer
Master II
Master II
Author

Thank's for your response, but is not that.

I explain more.with an example.

Table POS

CuentaFechaPosición
0128 9464 40 010001436211/10/2016            2.905,24

Table MOV

CuentaFechaImporte
0128 9464 40 010001436206/10/2016- 140,67 €
0128 9464 40 010001436206/10/2016- 128,37 €
0128 9464 40 010001436203/10/2016- 9,05 €
0128 9464 40 010001436203/10/2016- 111,77 €
0128 9464 40 010001436203/10/2016- 48,31 €
0128 9464 40 010001436203/10/2016- 24,47 €
0128 9464 40 010001436230/09/2016- 723,38 €
0128 9464 40 010001436230/09/2016- 18,15 €
0128 9464 40 010001436228/09/2016- 40,39 €
0128 9464 40 010001436228/09/2016- 364,39 €
0128 9464 40 010001436228/09/2016- 54,65 €
0128 9464 40 010001436228/09/2016- 737,42 €
0128 9464 40 010001436228/09/2016- 53,95 €
0128 9464 40 010001436226/09/2016- 162,21 €
0128 9464 40 010001436226/09/2016- 1.549,72 €
0128 9464 40 010001436221/09/2016- 284,12 €

The expected result I upload an excel example