Insert Missing Dates

[shamitshah]

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

[Kushal_Chawda]

see this

Generating Missing Data In QlikView

[ashishkalia]

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.

[shamitshah]

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