topic Re: Cumulation in Load Script => fill missing month postings in App Development

Cumulation in Load Script => fill missing month postings

ChrisGer — Fri, 17 May 2024 09:21:12 GMT

Hi Community,

I need your help on a load script.

I created this Qlik Script:

Table_CUM:

Load

Year,

Month,

YearMonth,

Order,

Orderpos,

Account,

Value,

if((Previous(Orderpos)=Orderpos and Previous(Account)=Account,rangesum(peek('Value_cum'),Value),Value) as Value_cum

Resident Table

Order by Orderpos ASC, Account ASC, YearMonth ASC;

This Script cumulates all the values; but I want all the Months being filled with cumulated results in Column Value_cum (see Result vs. Desired result). How could I achieve this?

Kind regards

Christian

Re: Cumulation in Load Script => fill missing month postings

Firefly_cam — Fri, 17 May 2024 20:20:43 GMT

Hey Christian,
I would suggest to use best practices of building data models, please check this out The As-Of Table .
But if you really insist on your variation:

InitialTable: Load * inline [ Year, Month, YearMonth, Order, Orderpos, Account, Value 2023, 07, 202307, 1002, 1002-700, Revenue, 384400 2023, 12, 202312, 1002, 1002-1000, Revenue, 1150000 ]; Join(InitialTable) Load Year(Date)*100+Month(Date) as YearMonth; Load Monthstart(DateMin,IterNo()-1) as Date While Monthstart(DateMin,IterNo()-1)<=DateMax; LOAD min(datefield) as DateMin, MonthStart(Today()) as DateMax ; LOAD MakeDate(left(FieldValue('YearMonth', RecNo()),4),Right(FieldValue('YearMonth', RecNo()),2)) as datefield AutoGenerate FieldValueCount('YearMonth'); ResultTable: Load *, if(Previous(Orderpos)=Orderpos and Previous(Account)= Account,rangesum(peek('Value_cum'),Value), Value) as Value_cum; Load num(left(YearMonth,4)) as Year, right(YearMonth,2) as Month, YearMonth, alt(Order,PEEK(Order)) as Order, Coalesce(Orderpos,PEEK(Orderpos)) as Orderpos, Coalesce(Account,PEEK(Account)) as Account, alt(Value,0) as Value; Load * Resident InitialTable Order By YearMonth asc; Drop table InitialTable;

Re: Cumulation in Load Script => fill missing month postings

ali_hijazi — Mon, 20 May 2024 11:01:53 GMT

you create a calendar from your minimum available date till today for example from the beginning of a desired year:

let StartDate = num(DayStart(YearStart(MakeDate(2000))));

let EndDate = num(daystart(monthend(Today())));

//Create a temporary calendar

TempCalendar:

load recno() as Date_Key,

'$(StartDate)'+recno()-1 as PeriodDate

autogenerate(EndDate-StartDate+1);

MasterCalendar:

load

Year(PeriodDate) as YEAR,

Month(PeriodDate) as MONTH,

Num(Month(PeriodDate)) as MONTH_NUM,

resident TempCalendar order by PeriodDate Asc;

Drop table TempCalendar;

then you outer join your fact table with this calendar

now you sort your fact table by account and date

if previous(account) <> account, amount, peek(cum_amount) + amount) as cum_amount

...