topic Join two tables in QlikView

Join two tables

Thu, 19 Apr 2012 10:12:40 GMT

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.

Join two tables

jagannalla — Thu, 19 Apr 2012 10:31:50 GMT

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.

Re: Join two tables

Thu, 19 Apr 2012 11:30:08 GMT

Thanks!!!

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

Best Regards

Join two tables

Thu, 26 Apr 2012 07:12:09 GMT

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

the graphic only shows dates with movements ...

thanks anyway ...

Best Regards

M.C.