How to count quantity values according to given conditions?
Hey Friends,
I am having problem to do the following task on Qlik-Sense.
I need to know the total quantity of values from the column LokID which the correspondent GWL Begin is less than MonthBegin_CD and GWLEnde is higher than MonthEnde_CD.
LokID not repeat but some dates are the same for different values on LokID.
The Master Calendar correspond fromm the years 2013 - 2028 with
The end result should be:
MonthBegin_CD MonthEnde_CD LoksInGuarantee (Values arer just Examples***)
01-Jan-2012 31-Jan-2012 568
01-Feb-2012 28-Feb-2012 658
01-Mar-2012 30-Mar-2012 987
01-Dic-2032 31-Dic-2032 985
Here is my code from load editor:
Fertigungsliste_02:
LOAD
"# - FL" as LokID_02,
"Lok-Nr - FL" as LokNummer_02,
Date("Ausgeliefert - FL", 'M/D/YYYY') as LiefDatum_02,
Date("Q40 Ende - FL", 'M/D/YYYY') as Q40Datum_02,
Date("GWL-Beginn - FL", 'M/D/YYYY') as GWLBegin_02,
Date("GWL-Ende - FL", 'M/D/YYYY') as GWLEnde_02,
AutoNumberHash128("# - FL"&'|'&"Lok-Nr - FL") as KeyCalendario_02,
"Halter - FL" as Holder_02,
"Kunde - FL" as Künde_02,
"Var. - FL" as Variante_02
FROM [lib://DataFiles/Datei_Quelle_probe.xlsx]
(ooxml, embedded labels, table is Fertigungsliste);
DataBridge_02:
Load
KeyCalendario_02 as KeyCalendario_02
,GWLBegin_02 as Canonical_Date_DB_02
,'GWLBegin_02' as FlagData_DB_02
Resident Fertigungsliste_02;
Concatenate(DataBridge_02)
Load
KeyCalendario_02 as KeyCalendario_02
,GWLEnde_02 as Canonical_Date_DB_02
,'GWLEnde_02' as FlagData_DB_02
Resident Fertigungsliste_02;
QuartersMap_02:
MAPPING LOAD
rowno() as Month_02,
'Q' & Ceil (rowno()/3) as Quarter_02
AUTOGENERATE (12);
Temp_02:
Load
date(min(Canonical_Date_DB_02), 'M/D/YYYY') as minDate_02,
date(max(Canonical_Date_DB_02), 'M/D/YYYY') as maxDate_02
Resident DataBridge_02;
Let varMinDate_02 = Num(Peek('minDate_02', 0, 'Temp_02'));
Let varMaxDate_02 = Num(Peek('maxDate_02', 0, 'Temp_02'));
DROP Table Temp_02;
TempCalendar_02:
LOAD
$(varMinDate_02) + Iterno()-1 As Num_02,
Date($(varMinDate_02) + IterNo() - 1, 'M/D/YYYY') as TempDate_02
AutoGenerate 1 While $(varMinDate_02) + IterNo() -1 <= $(varMaxDate_02);
MasterCalendar_02:
Load
TempDate_02 AS Canonical_Date_DB_02,
Year(TempDate_02) As Year_02,
Month(TempDate_02) As Month_02,
Day(TempDate_02) As Day_02,
Date(MonthStart(TempDate_02), 'M/D/YYYY') as MonthYearStart_02,
Date(MonthEnd(TempDate_02), 'M/D/YYYY') as MonthYearEnd_02,
Date(TempDate_02, 'M/YYYY') as MonthYear_02
Resident TempCalendar_02
Order By TempDate_02 ASC;
Drop Table TempCalendar_02;
Fertigungsliste:
LOAD
"# - FL" as LokID,
"Lok-Nr - FL" as LokNummer,
Date("Ausgeliefert - FL", 'M/D/YYYY') as LiefDatum,
Date("Q40 Ende - FL", 'M/D/YYYY') as Q40Datum,
Date("GWL-Beginn - FL", 'M/D/YYYY') as GWLBegin,
Date("GWL-Ende - FL", 'M/D/YYYY') as GWLEnde,
AutoNumberHash128("# - FL"&'|'&"Lok-Nr - FL") as KeyCalendario,
"Halter - FL" as Holder,
"Kunde - FL" as Künde,
"Var. - FL" as Variante
FROM [lib://DataFiles/Datei_Quelle_probe.xlsx]
(ooxml, embedded labels, table is Fertigungsliste);
Calendario_Temp:
Load
KeyCalendario as KeyCalendario,
GWLBegin as Canonical_Data,
'GWL_Begin' as Flag_Data
Resident Fertigungsliste;
Concatenate(Calendario_Temp)
LOAD
KeyCalendario as KeyCalendario,
GWLEnde as Canonical_Data,
'GWL_Ende' as Flag_Data
Resident Fertigungsliste;
MasterCalendar:
Load
KeyCalendario,
Flag_Data,
Canonical_Data,
Year(Canonical_Data) as Year,
Month(Canonical_Data) as Month,
Day(Canonical_Data) As Day,
Date(MonthStart(Canonical_Data), 'M/D/YYYY') as MonthYearStart,
Date(MonthEnd(Canonical_Data), 'M/D/YYYY') as MonthYearEnd,
Date(Canonical_Data, 'M/YYYY') as MonthYear
Resident Calendario_Temp;
Drop Table Calendario_Temp;
TempAsofCalendar:
Load
Distinct Month
Resident MasterCalendar;
Join (TempAsofCalendar)
Load
Month as AsofMonth
Resident TempAsofCalendar;
As_of_Calendar:
Load
Month,
AsofMonth,
Round((AsofMonth-Month)*12/365.2425) as MonthDiff,
.JPG){kind=link}
