topic Re: Dates Mapping to Actual Database in Water Cooler

Dates Mapping to Actual Database

sunilkumarqv — Thu, 20 Feb 2014 08:20:29 GMT

HI all

I have data excel loaded into qv in that data have some missing dates so i created dynamically dates .my question is how to map that dyanamically created dates to actual Data.

Re: Dates Mapping to Actual Database

MK_QSL — Thu, 20 Feb 2014 08:29:15 GMT

Can you be more specific in your question?

If possible provide sample file or apps to understand help you better.

Re: Dates Mapping to Actual Database

Thu, 20 Feb 2014 08:57:15 GMT

See Henric's blogs, you will find how to create a master calendar.

You need to create a table (master calendar) where you will have all the dates between the 1st and the last one of your data. For each date, you can compute many informations like the week day, the month, the Year, the YTD (or not) etc. Everything that will be useful in the UI

Fabrice

Re: Dates Mapping to Actual Database

Gysbert_Wassenaar — Thu, 20 Feb 2014 09:30:33 GMT

See this blog post: How to populate a sparsely populated field

Re: Dates Mapping to Actual Database

VishalWaghole — Thu, 20 Feb 2014 09:34:57 GMT

Hi Sunil,

Please find link,

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

it will help you.

- Regards

Vishal Waghole

Re: Dates Mapping to Actual Database

er_mohit — Thu, 20 Feb 2014 10:18:45 GMT

Create a ,aster calendar and link it with you actual date field making associative link to write the same name of that field like

// Calender

LET vDateMin = Num(MakeDate(2006,1,1));

LET vDateMax = Floor(DayEnd(Today()));

TempCalendar:

LOAD

date($(vDateMin) + RowNo() -1) AS DateNumber ,

Date($(vDateMin) + RowNo() -1) AS TempDate ,

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()<= $(vDateMax);

Calendar:

LOAD

Date(TempDate,'DD-MM-YYYY')as Date,

num(date(TempDate,'DD-MM-YYYY'))as num,

DayName(TempDate)as Day,

Day(TempDate) AS CalendarDayOfMonth,

YearName(TempDate,0,2)as FiscalYear,

Year(TempDate) AS YTD,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

MonthStart(TempDate) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

num(month(TempDate))as num_month,

YearStart(TempDate) as CalendarYearStart,

MonthEnd(TempDate) as LMTD,

QuarterEnd(TempDate) as LQTD,

(Date#(TempDate,'DD-MM-YYYY'))as Current_date,

YearEnd(TempDate) as LYTD

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Re: Dates Mapping to Actual Database

sunilkumarqv — Thu, 20 Feb 2014 10:29:51 GMT

Realy i uderstand somthing Gysbert but henric defined only dates b/w one column what incase have multiple columns

for that how to genarate.

Re: Re: Dates Mapping to Actual Database

MK_QSL — Thu, 20 Feb 2014 10:34:14 GMT

Check this ... it is about filling missing data for multiple field.

Re: Dates Mapping to Actual Database

sunilkumarqv — Thu, 20 Feb 2014 11:23:46 GMT

Thanks Manish its helped me .

Re: Re: Dates Mapping to Actual Database

MK_QSL — Thu, 20 Feb 2014 11:29:30 GMT

Use this file... It's very interesting and also related to you topic.

Re: Dates Mapping to Actual Database

sunilkumarqv — Thu, 20 Feb 2014 11:39:03 GMT

Thanks you all .You guys realy made the day cool!