How to change Week Number to Date

[#1]

Hi All,

I'm currently stuck in the middle to change from week number in X axis to Date.

Please advise me.

In this discussion I'm attached together with my source file and below my script.

Directory;

LOAD @1,

     @2,

     @3,

     @4,

     @5,

     @6,

     @7,

     @8,

     @9,

     @10,

     @11,

     @12,

     @13,

     @14,

     @15,

     @16,

     @17,

     @18,

     @19,

     @20,

     @21,

     @22,

     @23,

     @24,

     @25,

     @26,

     @27,

     @28,

     @29,

     @30,

     @31,

     @32,

     @33,

     @34,

     @35,

     @36,

     @37,

     @38,

     @39,

     @40,

     @41,

     @42,

     @43,

     @44,

     @45,

     @46,

     @47,

     @48,

     @49,

     @50,

     @51,

     @52,

     @53,

     @54,

     @55,

     @56

FROM

[..\..\..\QlikViewSupplyDemandSharedFolder\Weekly\MatchedPlan\MatchedPlan2011.xlsx]

(ooxml, no labels, header is 3 lines, table is Vegetable, filters(

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Top(1, 'Item_code'),

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'SKU'))),

Top(2, 'Budget'),

Top(2, 'Budgets'),

Remove(Row, RowCnd(Compound,

          RowCnd(CellValue, 2, StrCnd(contain, 'Matched', not)),

          RowCnd(CellValue, 2, StrCnd(contain, 'Budgets', not))

)),

Top(3, 'Remarks'),

Remove(Row, RowCnd(CellValue, 3, StrCnd(null))),

Remove(Row, RowCnd(Compound,

          RowCnd(CellValue, 3, StrCnd(contain, 'Remarks', not)),

          RowCnd(CellValue, 3, StrCnd(contain, 'Supply Demand', not))

))

))

Where (@1)='Item_code';

[#2]

If I use below statement it will shown the 1st date of the week which is Monday.

MakeWeekDate( '2011' , num(Mid(@4,6 ),'00')) AS @4,

MakeWeekDate( '2011' , num(Mid(@5,6 ),'00')) AS @5,

How should I do if the column auto create for 1 week by 7 days and the value also will be devide to 7 column base on the days meaning start from Monday to Sunday.

Requisitioner aid

Hairul

[swuehl]

You are probably referring to the values from the excel file attached to the prior thread with same name?

I would first try to read in the values from that file in each weekly field, so you get kind of crosstable (maybe you could read it in one LOAD?). Then I would use a crosstable load to transform your crosstable into a straight table.

If you need to create data by week day then, you could probably use a while statement in a resident load to create 7 records for each week day and divide the value by 7.

Hope this helps,

Stefan

[#4]

No Stefan is are different file. Attached is the excel file.

The 2nd paragraph means that you suggest me do the same step with previous discussion.

May I know the script for while that you mentioned.

Requisitioner aid

Hairul

[swuehl · Accepted solution]

Hairul,

I was thinking about something like at the end of this script snippet (the crosstable load is just to create some data, I think you know already how you can use this to get the data you want):

INPUT:

CrossTable(Week , Data)

LOAD * FROM

C:\Users\Stefan\AppData\Local\Opera\Opera\temporary_downloads\MatchedPlan2011.xlsx

(ooxml, embedded labels, table is Vegetable, filters(

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1)),

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 1)),

Remove(Row, Pos(Top, 2)),

Remove(Col, Pos(Top, 58)),

Remove(Col, Pos(Top, 57)),

Remove(Col, Pos(Top, 56)),

Remove(Col, Pos(Top, 58)),

Remove(Col, Pos(Top, 57)),

Remove(Col, Pos(Top, 56)),

Remove(Col, Pos(Top, 58)),

Remove(Col, Pos(Top, 57)),

Remove(Col, Pos(Top, 56)),

Remove(Col, Pos(Top, 58)),

Remove(Col, Pos(Top, 57)),

Remove(Col, Pos(Top, 56)),

Remove(Col, Pos(Top, 58)),

Remove(Col, Pos(Top, 57)),

Remove(Col, Pos(Top, 56))

))

WHERE(recno()<=2);

Result:

LOAD

F1, Week, Data,

MakeWeekDate( '2011' , num(Mid(Week,6 ),'00'),iterno()-1 ) AS Date,

Data / 7 as DailyData

resident INPUT while iterno()<=7;

drop table INPUT;

[#6]

Hi Stefan

Requisitioner aid:

1) Currently the script only read for Vegetable worksheet "(ooxml, embedded labels, table is Vegetable,)".  There is 2 more worksheet that need to be read with same condition and layout which is Premium Tomato and Tropical worksheet.  Please help me.

Question for previous script to have better understanding:

1) May I know the reason you recommend to use WHERE(recno()<=2) in crosstab.

2) I'm trying to understand the "MakeWeekDate" in help menu it very easy to understand; but yours "num(Mid(Week,6)" make me confuse, may I know where did you get "6" in this case, is it from Week character? If you pick from the Week character there will be 6 to 7 including space eg:Week 50. 

3) About the statement given, can you elaborate futher between a)(my statement) and b)(yours statement).  Statement a) the day will be on Monday but when used while function there will be no value. While your statement it's shown Sunday cause of  "-1" day without use while iterno()<=7, if used it while function it will shown start day from Monday till Sunday.

a) MakeWeekDate('2011',num(Mid(Weeks,6),'00')) as Date,                                                  //Date will shown start from Monday

b) MakeWeekDate('2011',num(Mid(Weeks,6 ),'00'),iterno()-1 ) AS Date,                    //Date will shown start from Sunday

    resident Weeks while iterno()<=7;

Awesome.....I'm impressed with your knowledge.

Requisitioner aid and explaination.

Hairul

[swuehl]

Hairul,

if you understand how to load in the table Vegetable, the other two should be quite straight forward, right?

1) I have not intented to say that you need to use where(recno()<=2). As said, "the crosstable load is just to create some data", so I limited it to some very few lines. Also I haven't spend much time on this, since the crosstable is already quite formatted (I personally would prefer to read in the used source data instead of the crosstable report), but you can make use of the script table wizard to read the crosstable in.

2) Well, num(mid(Week,6)) is essentially the same as num(mid(@4,6)) and that's the expression you stated above, right?

The format in your attached excel file seems to be 'Week ' (one trailing space) and then one or two digits for the week number, so above should work, but if your format differs, you might need to adapt for that.

3) Not sure if I understand the question, the third parameter indicates the weekday to create the date for of the given week. If you use while statement on load, you create new records (but from the same input record) while the given condition is true, and you can make use of iterno() to get the iterator of the while loop.

Hope this helps,

Stefan

[#8]

Stefan,

1) You mean I need to load another 2 crosstable?

Actually I'm thinking of to use for i, but it failed with appearing message "F2" table not found after I change vegetable to worksheet2 after table is

"ooxml, embedded labels, table is (change from vegetable to worksheet2,)"

2) As per your script MakeWeekDate script MakeWeekDate( '2011' , num(Mid(Weeks,7 ),'00'),iterno()-1 ) given, the start day will be from Monday till Sunday.  Within the 7 days I want it to exclude Saturday the day will appear are 6 days.  Please advise.

Requisitioner aid

Hairul

[swuehl]

Hi,

1) I don't see a worksheet2 in your attached excel file. Worksheets have name: 'Vegetable', 'Premium Tomato','Tropical','TMT Breakdown'

And I don't mean anything. You need to know what data you are looking for. I only noticed that yoru excel crosstable already contains some subtotal lines etc. so getting the data out of the excel file is probably some work to do (i.e. you probably need either use the filters on load to remove those lines or do some repeating load with limited lines or use something like sophisticated where clause or... That's why I said I personally would prefer reading in the original source data instead of the finished excel report (in case the original data is available).

I am personally not going to create the script to read the crosstable in for you, I even don't know what you are looking for in detail.

2) Either remove the Saturday in a following resident load (where weekday(Date)  <> 5) or you need to use a resident Weeks while iterno() <=6

and

MakeWeekDate( '2011' , num(Mid(Weeks,7 ),'00'),if(iterno()=6,6,iterno()-1) )

Hope this helps,

Stefan