topic Re: Insert Missing Dates in QlikView

Insert Missing Dates

shamitshah — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have missing dates, and hence not able to get the correct daily summary of quantity.

What is the best way to bring in the missing Record Dates?

The script is a follows:

HISTORY:

LOAD "SKH_ID",

"SKH_RecordDate",

Date([SKH_RecordDate] + MakeDate(2000,1,1)) as RecordDate,

Year(AddMonths(Date([SKH_RecordDate] + MakeDate(2000,1,1)),6)) as FiscalRecordYear,

Year(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordYear,

Month(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonth,

MonthName(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonthYr,

'Q' & (mod(Ceil(Month(Date([SKH_RecordDate] + MakeDate(2000,1,1)))/3)+1,4)+1) as RecordQuarter,

"SKH_StockID",

"SKH_Quantity",

SQL SELECT *

FROM "SKH_StockHistory";

Thanks

Shamit

Re: Insert Missing Dates

Kushal_Chawda — Thu, 19 May 2016 07:20:24 GMT

see this

Generating Missing Data In QlikView

Re: Insert Missing Dates

ashishkalia — Thu, 19 May 2016 07:41:25 GMT

Shamit,

When it comes to date, make sure to make a thumb rule "Use Master Calender".

What is master calender?

By Taking any date field, which is obviously the field you want to work on Months or Years, and link it with an artificially created table containing your dates and all time fields. This artificially created table is used for all time selections.

Take a look:-

in your case "SKH_RecordDate" date field looks like doing everything.

so do it like

Main:

Load *;

SQL SELECT *

FROM "SKH_StockHistory";

//...........Master Calender Tab

Temp:

load min(SKH_StockHistory]) as Date1,

max(SKH_StockHistory) as Date2

Resident Main;

Let vMin = peek('Date1',0,'Temp');

Let vMax = peek('Date2',0,'Temp');

Calander:

load Date(Date_num,'DD.MM.YYYY') as [SKH_StockHistory],

month(Date(Date_num,'DD.MM.YYYY')) as Month,

Num(month(Date(Date_num,'DD.MM.YYYY'))) as Month_Num,

Year(Date(Date_num,'DD.MM.YYYY')) as Year,

Week(Date(Date_num,'DD.MM.YYYY')) as Week;

load ($(vMin)+IterNo()-1) as Date_num

AutoGenerate(1)

while ($(vMin)+IterNo()-1)<= $(vMax);

Quater:

Load RowNo() as Month_Num,

'Q'&Ceil(RowNo()/3) as Qtr

AutoGenerate(12);

//...............................END...................................................

You can directly copy and paste the script if your sql load table is same with content and field name. Make sure to change the table name at resident command.

Cheers...........................

AK.

Re: Insert Missing Dates

shamitshah — Fri, 20 May 2016 04:15:02 GMT

Hi,

I did manage to get it by creating a temporary calendar and then a master calendar:

MasterCalendar:

load

TempDate AS RecordDate,

Year(AddMonths(Date([TempDate]),6)) as FiscalRecordYear,

Year(Date([TempDate])) as RecordYear,

Month(Date([TempDate])) as RecordMonth,

MonthName(Date([TempDate])) as RecordMonthYr,

'Q' & (mod(Ceil(Month(Date([TempDate]))/3)+1,4)+1) as RecordQuarter,

InYearToDate(TempDate,$(vToday),0)*-1 AS CurYTDFlag,

InYearToDate(TempDate,$(vToday),-1)*-1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

Thanks

Shamit