Data Extract from QVW file

[akpofureenughwu]

Hello,

I currently working on a dashboard and I am extracting data from an existing file.

I successfully extracted the data but I need to manipulate the field (to produce date fields like Month, Year)

I need tips on how to do this

Please see attachments for clarity..

Best Regards

Akpofure

[asgardd2]

Hello!

     Binary statement must be a first row in the script. (your first row is the "Calender:")

When you are loading data from another file, you are loading the data model too. So you may reload a table with dates from loaded data model(make the copy of table,transforming data,when delete original table and rename dublicate table) You can transforming date fileds with the functions for working with dates.(Date(),Date#())

[akpofureenughwu]

Binary ;

Load

CH.DOC.LAST_CHANGED_AT

"CH.DOC.LAST_CHANGED_AT" As date,

Month(CH.DOC.LAST_CHANGED_AT) as Month,

MonthName(CH.DOC.LAST_CHANGED_AT) as MonthName,

Year(CH.DOC.LAST_CHANGED_AT) as Year,

resident.

How can I get the name of the table? What if it doesn't have a table name...?

I do apply master calendar but this is my first attempt to load using the binary statment.

How do I implement this master calendar script in my work?

[akpofureenughwu]

Hello Anton, Thank you for your contribution..

Load date using binary statment is a new ground for me...

How do I implement date date(#) in this work?

[NickHoff]

Like the others suggested i'd use a master calendar.  I've included the script we use below.  You can modify several of the items like fiscal date to work for your company.  Once you add the calendar just join the %DATE_KEY to your Date Field.

//Calendar:

LET vDateMin = Num(MakeDate(2010,1,1)); 
//LET vDateMax = Floor(MonthEnd(Today())); 
LET vDateMax = Num(MakeDate(2020,6,30)); 
LET vDateToday = Num(Today()); 
LET PD = Date(makedate(2010,1,15));
LET vFactor = 6;   //offset for calculating fiscal year

TempCalendar: 
//Left Keep (PayCore)
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber, 
Date($(vDateMin) + RowNo() - 1) AS TempDate 
AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

SCM_CALENDAR_MASTER: 
LOAD
Date(TempDate) AS CalendarDate, 
Date(TempDate) AS  %DATE_KEY,

// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth, 
WeekDay(TempDate) AS CalendarDayName, 
Week(TempDate) AS CalendarWeekOfYear, 
Month(TempDate) AS CalendarMonthName, 
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter, 
Year(TempDate) AS CalendarYear, 

// Calendar Date Names 
WeekName(TempDate) as CalendarWeekNumberAndYear, 
MonthName(TempDate) as CalendarMonthAndYear, 
QuarterName(TempDate) as CalendarQuarterMonthsAndYear, 

// Start Dates 
DayStart(TempDate) as CalendarDayStart, 
WeekStart(TempDate) as CalendarWeekStart, 
MonthStart(TempDate) as CalendarMonthStart, 
QuarterStart(TempDate) as CalendarQuarterStart, 
YearStart(TempDate) as CalendarYearStart, 

// End Dates 
DayEnd(TempDate) as CalendarDayEnd, 
WeekEnd(TempDate) as CalendarWeekEnd, 
MonthEnd(TempDate) as CalendarMonthEnd, 
QuarterEnd(TempDate) as CalendarQuarterEnd, 
YearEnd(TempDate) as CalendarYearEnd, 

// Combo Date Examples 
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear, 
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter, 
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays, 

//Fiscal Dates
Num(Month(AddMonths(TempDate,$(vFactor))),00) AS FiscalMonthNum,
'FY ' & Right(Year(AddMonths(TempDate,$(vFactor))),2) AS FiscalYearDesc,
Year(AddMonths(TempDate,$(vFactor)))&'|'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as %Fiscal_Key,
Year(AddMonths(TempDate,$(vFactor)))&'-'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as FYRPR,
Year(AddMonths(TempDate,$(vFactor))) as FiscalYear,
'FY ' & Right(Year(AddMonths(TempDate,$(vFactor))),2)& ' Q' & Ceil(Month(AddMonths(TempDate,$(vFactor)))/3) AS FiscalYearQuarter

RESIDENT TempCalendar ORDER BY TempDate ASC; 

DROP TABLE TempCalendar; 

[akpofureenughwu]

Hello Sunny,

Can I send you a QVW file?

Would that help......

[akpofureenughwu]

Binary ;

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min(CH.DOC.LAST_CHANGED_AT) as minDate, 

               max(CH.DOC.LAST_CHANGED_AT) as maxDate 

Resident TEMP1; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS Date, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

   Drop Table TempCalendar; 

I need you to debug this script Please... Let's see if it work

[akpofureenughwu]

stalwar1‌

Binary ;

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min(CH.DOC.LAST_CHANGED_AT) as minDate, 

               max(CH.DOC.LAST_CHANGED_AT) as maxDate 

Resident TEMP1; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS Date, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

   Drop Table TempCalendar; 

Can you help me debug to see the challenge?

[akpofureenughwu]

Thanks Nick... I will review this script...

Can you help me debug this master calendar format

Binary ;

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min(CH.DOC.LAST_CHANGED_AT) as minDate, 

               max(CH.DOC.LAST_CHANGED_AT) as maxDate 

Resident TEMP1; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS Date, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

   Drop Table TempCalendar; 

Thanks

[sunny_talwar]

Try this:

Binary ;

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

              min(CH.DOC.LAST_CHANGED_AT) as minDate,

              max(CH.DOC.LAST_CHANGED_AT) as maxDate

Resident TEMP1;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

              $(varMinDate) + Iterno()-1 As Num,

              Date($(varMinDate) + IterNo() - 1) as TempDate

              AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

              TempDate AS CH.DOC.LAST....,

              week(TempDate) As Week,

              Year(TempDate) As Year,

              Month(TempDate) As Month,

              Day(TempDate) As Day,

              YeartoDate(TempDate)*-1 as CurYTDFlag,

              YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

              inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

              date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

              ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

              Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

              WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

  Drop Table TempCalendar;

Complete the name here: CH.DOC.LAST.... was not seeing the complete name in the image you posted above

[akpofureenughwu]

I was able to do this:

Still not working. Please help me debug it...

Binary ;

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min(CH.DOC.LAST_CHANGED_AT) as minDate, 

               max(CH.DOC.LAST_CHANGED_AT) as maxDate 

Resident TEMP1; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS Date, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

   Drop Table TempCalendar;