Metoder för kalender-skript

Det finns många metoder att hantera kalendrar i QlikView och Sense. I det här inlägget går jag igenom de vanligaste enkla fallen. Mer avancerade fall kommer i egna inlägg senare.

Kalender-skript går generellt ut på att skapa upp kalendervärden från ett datum. Om vi i vår datamodell till exempel har ett Fakturadatum och vill grafer och användare ska kunna arbeta med till exempel År, Månad, Vecka osv behöver vi skapa upp dessa fält.

En annan viktig del i ett kalender-skript är att alla möjliga värden skapas upp. Om vi haft servicejobb vecka 43 och 45 vill vi kunna se vecka 43, 44 och 45 i en graf. Normalt blir detta ett problem eftersom vi inte har några värden associerade med vecka 44.

Anta att vi har följande faktatabell:

Den äldsta metoden

Den äldsta metoden handlar om att generera fälten i den tabell där datumet finns. Värdena tas från från datumfältet och läggs som nya fält. Detta kan göras i den load-sats som läser in data från källan.

Load
    FakturaNr,
    KundNr,
    Fakturadatum,
    Fakturabelopp,
    year(Fakturadatum) as År,
    'Q' & ceil(month(Fakturadatum)/3) as Kvartal,
    month(Fakturadatum) as Månad,
    week(Fakturadatum) as Vecka,
    year(Fakturadatum) & num(month(Fakturadatum),'00') as ÅrMånad,
    weekyear(Fakturadatum) & num(month(Fakturadatum),'00') as ÅrVecka
from Fakturor.qvd (qvd);

Från Fakturadatum beräknas flera kalenderfält:

Nackdelen med denna metod är dels att vi inte genererar upp värden för mellanliggande dagar. I exemplet ovan har vi inte haft någon försäljning i 2019 november och då kommer vi heller inte ha värdet 201911 i ÅrMånad-fältet.

En annan nackdel är att eftersom fälten finns i faktatabellen kommer kalendervärdena behöva refereras på många rader. Anta till exempel att vi har tio års data med 52 veckor per år. Då finns 520 unika ÅrVecka-värden. Qliks motor hanterar de unika värdena separat och skapar i faktatabellen en pekare till dessa. För att unikt peka ut 520 unika värden behövs 10 bitar (2^9 = 512, 2^10 = 1024). Om tabellen innehåller 100 miljoner rader kommer vi behöva 100 miljoner pekare om 10 bitar vardera = 1 miljard bitar = 119MB data.

För små applikationer där det inte är viktigt att fylla ut värden mellan datum går denna metod bra.

Qliks kalender-skript

Vid något tillfälle släppte Qlik ett mer eller mindre standardskript för att generera upp en kalender. Skriptet blev snabbt populärt och spred sig i Qlik-världen. Skriptet går ut på att hitta det första och sista datumet i datumfältet och sedan generera alla dagar där mellan samt dess kalendervärden. Kalendervärdena lagras i en separat tabell, vilket effektiviserar om man har en stor faktatabell.

Här följer en kortare version av Qliks kalender-skript:

// Ladda in faktatabellen
Fakturor:
Load
    FakturaNr,
    KundNr,
    Fakturadatum,
    Fakturabelopp
from Fakturor.qvd (qvd);

// Hitta första och sista datumet
MinMax:
Load
    num(min(Fakturadatum)) as MinFakturadatum,
    num(max(Fakturadatum)) as MaxFakturadatum
resident Fakturor;

// Flytta in värdena i variabler
Let vMinDatum = peek('MinFakturadatum',0);
Let vMaxDatum = peek('MaxFakturadatum',0);
drop table MinMax;

// Generera upp kalendern
Kalender:
Load
    Fakturadatum,
    year(Fakturadatum) as År,
    'Q' & ceil(month(Fakturadatum)/3) as Kvartal,
    month(Fakturadatum) as Månad,
    week(Fakturadatum) as Vecka,
    year(Fakturadatum) & num(month(Fakturadatum),'00') as ÅrMånad,
    weekyear(Fakturadatum) & num(month(Fakturadatum),'00') as ÅrVecka
;
Load
    date($(vMinDatum)+recno()-1,'YYYY-MM-DD') as Fakturadatum
autogenerate($(vMaxDatum)-$(vMinDatum)+1);

Förbättrad prestanda

2015 presenterade Rob Wunderlich en alternativ metod för att läsa problemet med att hitta första och sista datumet i fältet. Min/max-metoden var inte så effektiv i stora tabeller då Qlik behövde gå igenom alla rader för att hitta det minsta och största värdet.

Genom att titta på de unika fältvärdena kunde de minsta och största datumen hittas mycket snabbare. Resten av koden kan vara som tidigare även om Rob föreslog ett alternativ även här.

// Hitta första och sista datumet
MinMax:
Load
    num(fieldvalue('Fakturadatum')) as MinFakturadatum,
    num(fieldvalue('Fakturadatum')) as MaxFakturadatum
autogenerate fieldvaluecount('Fakturadatum');

Denna metod är mycket effektivare på stora faktatabeller med många rader. Jag hade själv ett exempel där Qliks skript tog tre timmar att exekvera för att hitta min/max och med denna nya metod gick tiden ner till tre sekunder.