Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
akpofureenughwu
Creator III
Creator III

Data Extract from QVW file

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

22 Replies
asgardd2
Creator III
Creator III

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Specialist
Specialist

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
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
Creator III
Creator III
Author

Hello Sunny,

Can I send you a QVW file?

Would that help......

akpofureenughwu
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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;