If condition in LOAD statement...Clarification Needed!

[#1]

Hi all,

Consider the following data:

WeekID	WeekSeq	POSQty
201034	-39	47
201035	-38	83
201036	-37	74
201037	-36	7
201038	-35	7
201039	-34	53
201040	-33	93
201041	-32	9
201042	-31	36
201043	-30	29
201044	-29	48
201045	-28	68
201046	-27	72
201047	-26	32
201048	-25	60
201049	-24	44
201050	-23	66
201051	-22	67
201052	-21	30
201101	-20	74
201102	-19	68
201103	-18	69
201104	-17	89
201105	-16	6
201106	-15	68
201107	-14	3
201108	-13	31
201109	-12	75
201110	-11	89
201111	-10	87
201112	-9	69
201113	-8	37
201114	-7	71
201115	-6	43
201116	-5	44
201117	-4	79
201118	-3	97
201119	-2	38
201120	-1	88

I have a Load statement as follows:

---------------------- CODE START -------------------------------------

STORE_SALES:

LOAD *,

    0                                                                                                    as STORE_SALES.Input,

   if(STORE_SALES.RetailerMaxYear = STORE_SALES.Year, STORE_SALES.QtyPOS, null())                         as STORE_SALES.QtyPOS_YTD,

    if(STORE_SALES.RetailerMaxYear -1 = STORE_SALES.Year

    AND STORE_SALES.RetailerMaxWeek >= STORE_SALES.Week, STORE_SALES.QtyPOS, null())                     as STORE_SALES.QtyPOS_LYTD,

    if(STORE_SALES.WeekSequence =  -1, STORE_SALES.QtyPOS, null())                                         as STORE_SALES.QtyPOS_LastWeek,

    if(STORE_SALES.WeekSequence =  -2, STORE_SALES.QtyPOS, null())                                         as STORE_SALES.QtyPOS_PreviousWeek,

    if(STORE_SALES.WeekSequence >= -4, STORE_SALES.QtyPOS, null())                                         as STORE_SALES.QtyPOS_Last4Weeks,

    if(STORE_SALES.WeekSequence >= -8, STORE_SALES.QtyPOS, null())                                         as STORE_SALES.QtyPOS_Last8Weeks

;

LOAD

     WeekID,

     WeekSeq,

     POSQty as STORE_SALES.QtyPOS,

   //---------------------------------TRENDS------------------------------

    num(left(STORE_SALES.WeekID, 4))                                                             as STORE_SALES.Year,

    num(right(STORE_SALES.WeekID, 2))                                                            as STORE_SALES.Week,

RESIDENT TEMP_STORE_SALES;

DROP TABLE TEMP_STORE_SALES;

----------------------- CODE END ------------------------

I understand what the if condition is attempting to do. Depending on the weekseq number it calculates the LastWeek, PrevWeek etc.

My question is how does the IF condition examine all the loaded data in order to calculate the LastWeek, PrevWeek, Last4Week for a given record. Can any one provide an insight since I don't quite understand how the dataset is processed for each of the record since there are no loop conditions.

Best regards,

T.

[swuehl]

The code you posted does not aggregate the values for the given periods, it just introduce fields that contain that data, but still on a weekly, most granular base.

So you would need to e.g. use sum(STORE_SALES.QtyPOS_Last8Weeks) to get the aggregated values for the last 8 weeks.

It is similar to use flags to indicate the records that belong to certain periods, but instead of using binary flags, the above code copies the actual values, so you could just sum the field.

Hope this helps,

Stefan

[#3]

You are absolutely correct. I believe the application uses the sum of Last8Weeks field. I was wondering how does the IF condition work.

For example if I look at the data for 201120. I know that I can use the weekseq to find the prev week etc but how does the LOAD condition does the look up for the Prev Week when I'm loading the record for 201120. It's almost as if I do a load with using a WHERE condition on the WeekSeq.

I'm having difficulty understanding the looping thats done buy the load statement.

Bottom line is I would like to know what is the best way to look up data based on a condition from loaded dataset. As I said for the record for weekID 201120, I would like to add a field for previous week POSQty and Last4Weeks POSQty.

So in the end my data would look similar to

WeekID, WeekSeq, POSQty, PrevWeekPOSQty, Last4WeeksPOSQty

201120, -1, 88, 38, 302

I appologize if I'm not being clear.

T

[swuehl]

You could use peek() or previous() function to access records that have already been read in.

For example, to sum up the last 2 or read in POSQty:

...

rangesum(QtyPOS, peek(QtyPOS)) as Last2Weeks,

rangesum(QtyPOS, peek(QtyPOS),peek(QtyPOS,RecNo()-3),peek(QtyPOS,RecNo()-4)) as Last4Weeks

...

// recno() starts indexing with 1, peek() with zero //

Hope this helps,

Stefan

[#5]

This makes a lot more sense. Thanks!

Can the same peek() and previous() techniques be used for more complex data. For example:

WeekID	UPC	WeekSeq	POSQty
201110	11	−1	9
201110	22	−1	89
201110	33	−1	65
201110	44	−1	36
201109	11	−2	62
201109	22	−2	87
201109	33	−2	24
201109	44	−2	90
201108	11	−3	50
201108	22	−3	7
201108	33	−3	65
201108	44	−3	42
201107	11	−4	36
201107	22	−4	70
201107	33	−4	56
201107	44	−4	60
201106	11	−5	59
201106	22	−5	9
201106	33	−5	62
201106	44	−5	22

[swuehl · Accepted solution]

I assume you want to aggregate the last 2 / last 4 Weeks per UPC then?

If so, you could just use the same approach, you just need to sort by UPC, WeekID accordingly and take care of the segment borders (change of UPC):

INPUT:

LOAD WeekID,

           UPC,

     WeekSeq,

     POSQty as QtyPOS

FROM

[http://community.qlik.com/message/183312]

(html, codepage is 1252, embedded labels, table is @2);

Result:

LOAD *,

recno() as RecID,

rangesum(QtyPOS, if(peek(UPC)=UPC,peek(QtyPOS))) as Last2Weeks,

rangesum(QtyPOS, if(peek(UPC)=UPC,peek(QtyPOS)),

           if(peek(UPC,recno()-3)=UPC and recno()>2,peek(QtyPOS,recno()-3)),

           if(peek(UPC,recno()-4)=UPC and recno()>3,peek(QtyPOS,recno()-4))) as Last4Weeks

Resident INPUT order by UPC, WeekID asc;

DROP TABLE INPUT;

[#7]

Thank you good sir! This works perfectly.

Now we continue to add more dimensions such as for each UPC we add Stores and so on what is the best aproach to sum POS values for past 2 weeks or past 4 weeks?

Also is there a way that we can create a summary table based on input table?

T

[swuehl]

You could create a new field for grouping, like

autonumber(UPC&Stores) as GroupingID

(add more fields to concatenation as you need). Then replace UPC in above Result load by GroupingID.

Not sure what you mean by 'create a summary table based on input table'. Could you explain that.

Honestly, I wouldn't recommend creating lot of precalculated summaries. QlikView is quite good in summing up values on the fly. You will probably lose some flexibilty if you focus too much on precalculation, since you won't do that for all combinations of dimension groupings / slicing.

Sometimes it is good to precalculate stuff though for performance reason.

[#9]

Works! I realize that in order to use my initial method I needed to group the dimensions. sum function only worked when I grouped the WeekID along with UPC etc.

T