Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
May be making sure that this is not a timestamp field and actually a date field?
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
How about this
floor([OrderTimestamp]) as OrderDate
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