topic Re: Data Extract from QVW file in QlikView

Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 14:50:45 GMT

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

Re: Data Extract from QVW file

sunny_talwar — Thu, 28 Jul 2016 14:54:27 GMT

I guess create a Master Calendar which links to your field you want it to link to.

Re: Data Extract from QVW file

maheshkuttappa — Thu, 28 Jul 2016 14:55:00 GMT

Calender

Load

<Date.Filed Name> As date

Month(<Date.Filed Name>) as Month

MonthName<Date.Filed Name> ) as MonthName

Year(<Date.Filed Name> ) as Year.

resident <tableName

Re: Data Extract from QVW file

Anonymous — Thu, 28 Jul 2016 14:55:31 GMT

You could add a new Mater Calendar table Creating A Master Calendar‌in your and join that to the date field from your binary load.

Re: Data Extract from QVW file

maheshkuttappa — Thu, 28 Jul 2016 14:56:05 GMT

Calender

Load

Date.Filed Name

<Date.Filed Name> As date

Month(<Date.Filed Name>) as Month

MonthName<Date.Filed Name> ) as MonthName

Year(<Date.Filed Name> ) as Year.

resident <tableName>

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 14:58:13 GMT

Thanks Sunny for your quick response and good suggestions.

Can you help me with tips on how to achieve that..

I tried introducing scripts after the binary statement.

It doesn't response...

Re: Data Extract from QVW file

sunny_talwar — Thu, 28 Jul 2016 15:02:45 GMT

Can you share the script you have tried?

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 15:09:03 GMT

Thanks M. Kumar

Please I try this script you suggest, It didn;t load..

Please find attached file....

Maybe I didn't arrange the syntax well...

The table name is the one I will give to the qvw file.

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 15:10:54 GMT

Please find attachment

Re: Data Extract from QVW file

maheshkuttappa — Thu, 28 Jul 2016 15:29:52 GMT

Few things are not right

Remove "Calender: " Before Binary load ( Please read the concept of binary load and its limitation)

Then below script

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 "Name of the table in which field CH.DOC.LAST_CHANGED_AT is present after binary load "

As Sunny and Bill suggested Master calendar is way to go..

Re: Data Extract from QVW file

asgardd2 — Thu, 28 Jul 2016 15:34:28 GMT

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#())

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 15:47:44 GMT

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?

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 15:49:38 GMT

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?

Re: Data Extract from QVW file

NickHoff — Thu, 28 Jul 2016 15:52:31 GMT

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;

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 16:18:04 GMT

Hello Sunny,

Can I send you a QVW file?

Would that help......

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 16:29:01 GMT

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

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 16:30:15 GMT

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?

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 16:32:17 GMT

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

Re: Data Extract from QVW file

sunny_talwar — Thu, 28 Jul 2016 16:33:16 GMT

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

Re: Data Extract from QVW file

akpofureenughwu — Thu, 28 Jul 2016 16:33:34 GMT

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;