problem with join from 2 tables with date field

Thu, 29 Jan 2015 07:25:14 GMT

Hello,

I try now several days to link 2 tables with different data and the key field can only be a date.

the first table "MWOPTR_Rückmeldungen:" has a generated datum field from a timestamp

If(date(date#(DJTRDT, 'YYYYMMDD'), 'DD.MM.YYYY')>='01.01.2014',date(date#(DJTRDT, 'YYYYMMDD'), 'DD.MM.YYYY')) as M3Datum

the second table called "Roboterladen:" and has also a generated datefield from a timestamp

date(Zeitpunkt, 'DD.MM.YYYY') as M3Datum

The table roboterladen is generated by a left join with a mastercalendar table based on all dates between 1.1.2014 and the max date out of the M3Datum field from table MWOPTR_Rückmeldungen:

Temp:

LOAD

Min(M3Datum) as minDate,

Max(M3Datum) as maxDate

Resident MWOPTR_Rückmeldungen;

let varminDate=num(Peek('minDate',0,'Temp'));

let varmaxDate=num(Peek('maxDate',0,'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varminDate)+IterNo()-1 as Num,

Date($(varminDate)+IterNo()-1) as TempDate

AutoGenerate 1 While $(varminDate)+IterNo()-1<=$(varmaxDate);

RoboterdataCalendar:

LOAD

TempDate as M3Datum,

week(TempDate) as M3Woche,

Year(TempDate) as M3Jahr,

month(TempDate) as M3Monat,

day(TempDate) as M3Tag

Resident TempCalendar

Order by TempDate ASC;

DROP Table TempCalendar;

LOAD

M3Datum,

M3Woche,

M3Jahr,

M3Monat,

M3Tag

Resident RoboterdataCalendar;

left Join(RoboterdataCalendar)

LOAD

all fields and

date(Zeitpunkt, 'DD.MM.YYYY') as M3Datum,

SQL SELECT *

FROM ProdDataSlim.dbo."!Data21Line";

The tables gets linked but if i use dimensions in diagrams which are based on the M3Datum field, i get emtpy data or wrong data.

the correct value is only coming from the calculation based on table "MWOPTR_Rückmeldungen:", but all data calculated from "Roboterladen:" but should be nearly the same values.

Has somebody an idea what went wrong?

Thanks Armin

topic problem with join from 2 tables with date field in QlikView

problem with join from 2 tables with date field