Mastercalender issue with concatenate

[francoisreinert]

Hi,

i am a complete beginner in the subject of qliksense. However I have taught myself a lot of things quite well. But now I come across a problem, which I cannot solve.

I load a date field from 2 different data sources, among other things, which I merge into a table using "concatenate".

The field in question is:   OrderDate

[cutlist_order_temp_74e4f8a6-f394-50f5-f057-d0388bfb]:
LOAD
	[OrderId],
	[Organisation],
	[OrderTimestamp],
    date(floor([OrderTimestamp]/86400+25569)) as [OrderDate]
	
RESIDENT [cutlist_order] where [OrderTimestamp] >= '1577836800';
DROP TABLE [cutlist_order];
RENAME TABLE [cutlist_order_temp_74e4f8a6-f394-50f5-f057-d0388bfb] to [cutlist_order];

LIB CONNECT TO 'ReportingDB (netdfa_svcdfaadmin)';
Concatenate
LOAD auftragsnummer as [OrderId],
	kunde as [Organisation],
        auftragsannahme as [OrderDate];
SQL SELECT auftragsnummer,
           kunde,
           auftragsannahme
FROM sportcast.reporting;

Mastercalender:

Set vFM = 7 ;  
Set vFD = 0;

QuartersMap:  
MAPPING LOAD   
rowno() as Month,  
'Q' & Ceil (rowno()/3) as Quarter  
AUTOGENERATE (12);  
      
Temp:  
Load  
min(OrderDate) as minDate,  
max(OrderDate) as maxDate  
Resident cutlist_order; 
      
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);  
      
MasterCalendar:  
Load distinct
   Dual(fYear-1 &'/'& fYear, fYear) as FYear,   // Dual fiscal year
   Dual(Month, fMonth)                as FMonth,  // Dual fiscal month
   Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
   Ceil((OrderDate-StartOfFWeekOne+1)/7) as FWeekNo,
   *;
Load Year + If(Month>=$(vFM), 1, 0)   as fYear,   // Numeric fiscal year
   Mod(Month-$(vFM), 12)+1            as fMonth,  // Numeric fiscal month
   Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,
   WeekStart(FYearStart,0,$(vFD))     as StartOfFWeekOne,
   *;
Load  
  TempDate AS OrderDate,  
  YearStart(TempDate,0,$(vFM)) as FYearStart,
   Year(TempDate)               as Year,
   Month(TempDate)              as Month,
   Date(Monthstart(TempDate), 'MMM-YYYY') as MonthYear,
   Week(TempDate)               as ISOWeekNo,
   Dual(WeekDay(TempDate),Mod(WeekDay(TempDate-$(vFD)),7)+1) as WeekDay,
   Day(TempDate)                as Day,
   Date(TempDate, 'MM/DD')      as DATEMMDD

Resident TempCalendar  
Order By TempDate ASC;  
Drop Table TempCalendar;  

But if I now do the evaluation according to the master calendar, I miss everything from the table that was merged via concatenate.

The OrderDate was correctly merged into the table "cutlist_order". But the master calendar which has the "cutlist_order" as source (resident) seems to take the values only from the table without the concatenate.

Any ideas to fix this?

kindest regards

Francois

[sunny_talwar]

May be you need to transform auftragsannahme just like you do it in RESIDENT [cutlist_order]

Concatenate ([cutlist_order])
LOAD auftragsnummer as [OrderId],
	kunde as [Organisation],
        date(floor(auftragsannahme/86400+25569)) as [OrderDate];
SQL SELECT auftragsnummer,
           kunde,
           auftragsannahme
FROM sportcast.reporting;

[francoisreinert]

Hi,

thanks for sharing your idea. But as you can see, correct date is published in concatenated "OrderDate" from  cutlist_order. Only the Mastercalender has problems to get the information from the concatenated table. 

First row is from reference "cutlist_order"

Rest is from concatenated list.

[sunny_talwar]

May be making sure that this is not a timestamp field and actually a date field?

[francoisreinert]

Didn't get it. I already did this with:

date(floor([OrderTimestamp]/86400+25569)) as [OrderDate]

all other OrderDate Fields are dates as well.

best

Francois

[sunny_talwar]

How about this

floor([OrderTimestamp]) as OrderDate

[lorenzoconforti]

Just to make sure those concatenated fields are actually loaded as dates, maybe add another field to the table below as =date(OrderDate)

just to be completely sure those are dates