Join two tables

[#1]

Hello!!

I have two tables:

- accounting transactions 

- MasterCalendar

My problem is the next:

An account has movements but not all the dates but I need to get the account balance every day.

If I make a pivot table chart with date dimension only shows me the dates where there has been movement, But I need to show me every day with the accumulated.

For example,

Account: 5720000001

Movements:

01/01/2012     100

02/01/2012     200

02/01/2012     -50

05/01/2012    150

07/01/2012      -10

The chars would have to show :

                         01/01/2012     02/01/2012     03/01/2012     04/01/2012     05/01/2012      06/01/2012     07/01/2012

5720000001          100                    250          250               250                    400               400                    390

Do not know how to show all dates in the calendar and join them with the date of the accounting record.

F_ECRITUREC:

LOAD *, IF (Apunte_Sentido=0,Apunte_Importe,0) AS Apunte_Debe,

IF (Apunte_Sentido=1,Apunte_Importe,0) AS Apunte_Haber;

SQL SELECT "CG_NUM" as General_Numero,

    "CT_NUM" as Tercero_Numero,

    (EC_JOUR + JM_DATE)-1 AS Fecha,

    "EC_MONTANT" as Apunte_Importe,

    "EC_SENS" as Apunte_Sentido,

    "JO_NUM" as Diario,

    "EC_PIECE" as Apunte_Asiento,

    "EC_INTITULE" as Apunte_Descripcion,

    "EC_REFPIECE" as Apunte_Factura,

    "EC_ECHEANCE" AS Apunte_Vencimiento,

    "N_REGLEMENT" AS CodPago,

   "EC_LETTRE" AS Compensacion,

   "EC_NO" AS NumInterno

FROM F_ECRITUREC ORDER BY JM_DATE ASC;

MasterCalendar:

LOAD

TempDate AS Fecha,

Day(TempDate) AS Dia_Numero,

WeekDay(TempDate) AS Dia_Nombre,

Week(TempDate) AS Semana_Numero,

Month(TempDate) AS Mes_Nombre,

Year(TempDate) AS Año,

'T' & Ceil(Month(TempDate)/3) AS Trimestre_Nombre,

WeekDay(TempDate) & '-' & Year(TempDate) AS Semana_año,

if(Year(TempDate) = Year($(vAñoActual)), 1, 0) as Año_Actual,

if(Year(TempDate) = Year($(vAñoActual))-1, 1, 0) as Año_N1,

if(Year(TempDate) = Year($(vAñoActual))-2, 1, 0) as Año_N2,

Month(TempDate) & '-' & Year(TempDate) AS Mes_Año

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Thanks in advance

M.C.

[jagannalla]

MasterCalender:

Load *,Fecha

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Left Join(MasterCalender)

LOAD *, IF (Apunte_Sentido=0,Apunte_Importe,0) AS Apunte_Debe,

IF (Apunte_Sentido=1,Apunte_Importe,0) AS Apunte_Haber;

SQL SELECT "CG_NUM" as General_Numero,

    "CT_NUM" as Tercero_Numero,

    (EC_JOUR + JM_DATE)-1 AS Fecha,

    "EC_MONTANT" as Apunte_Importe,

    "EC_SENS" as Apunte_Sentido,

    "JO_NUM" as Diario,

    "EC_PIECE" as Apunte_Asiento,

    "EC_INTITULE" as Apunte_Descripcion,

    "EC_REFPIECE" as Apunte_Factura,

    "EC_ECHEANCE" AS Apunte_Vencimiento,

    "N_REGLEMENT" AS CodPago,

   "EC_LETTRE" AS Compensacion,

   "EC_NO" AS NumInterno

FROM F_ECRITUREC ORDER BY JM_DATE ASC;

Hope it helps you.

[#3]

Thanks!!!

I´ll test it, although I tried something similar and It did not work ...

Best Regards

[#4]

I have tried but It doesn´t  work  ..... 

the graphic only shows dates with movements ...

thanks anyway ...

Best Regards

M.C.