topic Re: Generate Week Numbers as Filed during resident load in QlikView

Generate Week Numbers as Filed during resident load

agni_gold — Sat, 30 Aug 2014 09:31:32 GMT

I gave a table , contains Week start date and week end date and other columns ,

But i want to load the resident as week numbers also

9.........

52 as Week field.

How can i do that ....

thanks

Re: Generate Week Numbers as Filed during resident load

MarcoWedel — Sat, 30 Aug 2014 09:43:20 GMT

LOAD Week(WeekStartDate) as Week

WeekStartDate,

WeekEndDate,

OtherColumns

From YourDataSource;

Re: Generate Week Numbers as Filed during resident load

MarcoWedel — Sat, 30 Aug 2014 09:44:31 GMT

There are also other helpful functions regarding Weeks:

WeekYear, WeekName

hope this helps

regards

Marco

Re: Generate Week Numbers as Filed during resident load

agni_gold — Sat, 30 Aug 2014 09:57:52 GMT

Thanks for your reply Macro.

I have already used it but problem is that it generates only week start numbers

But i need 1 to 49 series , the data related to week 1 is came for 2,3,4 also .

Re: Generate Week Numbers as Filed during resident load

ecolomer — Sat, 30 Aug 2014 10:15:26 GMT

You can generate number from week(01/01/year) to week(31/12/year)

Re: Generate Week Numbers as Filed during resident load

agni_gold — Sat, 30 Aug 2014 10:28:19 GMT

inside load ?

Please give me some script.

Re: Generate Week Numbers as Filed during resident load

ecolomer — Sat, 30 Aug 2014 10:29:32 GMT

// -----> 41275 = 01/01/2013

// -----> 41640 = 01/01/2014

Orders:

Load

date(num(41275) + ceil(RowNo()/3)-1) as Date,

RowNo() as OrderNo,

if(odd(RowNo()),'Ext','Int') as OrderType,

if(right(RowNo()*2,1)=2,'Joan','Anna') as OrderCreator,

rand() * 1000 as OrderValor

autogenerate 2190;

//------------ FECHAS / Dates

LOAD

Date as xDate,

year(Date) as xYear,

month(Date) as xMonth,

day(Date) as xDay,

Num(week(Date), '00') as xWeek,

'Q' & ceil(Num(Month(Date))/3) as xQuarter,

'H' & ceil(Num(Month(Date))/6) as xHalf,

Num(Month(Date), '00') as xMonthNo,

MonthName(Date) as xMonthYear,

QuarterName(Date) as xQuarterYear,

year(Date)&'-Q'&ceil(Num(Month(Date))/3) as xYearQuarter,

year(Date)&'-'&Num(Month(Date),'00') as xYearMonth,

WeekYear(Date)&'-'&Num(Week(Date),'00') as xYearWeek,

WeekStart(Date) as xWeekStart,

WeekEnd(Date) as xWeekEnd,

WeekYear(Date) as xWeekYear,

dual(WeekDay(Date),num(WeekDay(Date))) as xWeekDay

resident Orders;

Re: Generate Week Numbers as Filed during resident load

ecolomer — Sat, 30 Aug 2014 10:30:16 GMT

This script have other fields but I think is good for you

Re: Re: Generate Week Numbers as Filed during resident load

ecolomer — Sat, 30 Aug 2014 10:34:22 GMT

This is the QV file, with another elements

Re: Re: Generate Week Numbers as Filed during resident load

MarcoWedel — Sat, 30 Aug 2014 10:42:03 GMT

you probably need a calendar table.

Maybe this helps:

tabWeekCalendar:
LOAD *,
      Week(MakeWeekDate(2014, IterNo())) as Week,
      WeekYear(WeekDate) as WeekYear,
      WeekName(WeekDate) as WeekName;
LOAD MakeWeekDate(2014, IterNo()) as WeekDate
AutoGenerate 1
While IterNo() <= 52;

regards

Marco

Re: Re: Re: Generate Week Numbers as Filed during resident load

ecolomer — Sat, 30 Aug 2014 10:47:21 GMT

Probably this one is more clair for you

Re: Generate Week Numbers as Filed during resident load

agni_gold — Sat, 30 Aug 2014 10:52:08 GMT

Yes but you are creating saprate table but i need same thing during resident load from source table.

Re: Generate Week Numbers as Filed during resident load

MarcoWedel — Sat, 30 Aug 2014 11:07:36 GMT

Please post example data and desired output.

Re: Generate Week Numbers as Filed during resident load

agni_gold — Sat, 30 Aug 2014 11:08:42 GMT

How you decided

autogenerate 2190;

2190 value

Re: Re: Generate Week Numbers as Filed during resident load

agni_gold — Sat, 30 Aug 2014 11:18:25 GMT

I am attaching excel for data .

excel contains two sheets sorce data and desired output please have a look

Re: Re: Generate Week Numbers as Filed during resident load

agni_gold — Sat, 30 Aug 2014 11:23:09 GMT

How you decided

autogenerate 2190;

2190 value

Re: Re: Re: Generate Week Numbers as Filed during resident load

MarcoWedel — Sat, 30 Aug 2014 12:26:18 GMT

Hi,

maybe like this?:

LOAD *,
    Week([Month Date]) as Week;
LOAD Date([Month Date], 'MMM') as month,
    Year,
    value,
    Date([Month Date]+IterNo()-1) as [Month Date]
While [Month Date]+IterNo()-1 <= MonthEnd([Month Date]);
LOAD *,
    Date(Date#('1-'&month&'-'&Year)+IterNo()-1) as [Month Date]
FROM [http://community.qlik.com/servlet/JiveServlet/download/598314-122554/Data.xlsx] (ooxml, embedded labels, table is [Sorce Data]);

hope this helps

regards

Marco

Re: Generate Week Numbers as Filed during resident load

ecolomer — Sun, 31 Aug 2014 07:51:02 GMT

This is only for tests

Re: Generate Week Numbers as Filed during resident load

ecolomer — Sun, 31 Aug 2014 08:42:57 GMT

Is only for tests, and is 2190 = 365 (year) x 6