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