Mapping Load doesn't work within a loop?

[mrooney]

Hi there,

I've got a mapping load within a loop in order to pick the customer name for  transactions made in a range of dates. So, in that loop I've got a mapping load for the customer_id and after that a load with an Applymap. The problem is that the mapping doesn't work properly. I gues the problem is that a mapping whitin a loop concatenates the data gathered from each loop. But since it is not possible to drop a mapping table I don't know how to go over this problem.  I've already tried adding a numeric value to the customer_id in each loop, but the tables are so large that performance is no acceptable. Any clues?

Tanks to all of you.

[rbecher]

Hi Miguel,

was the join made in QlikView or in database? Why you're doing a loop at all? Typically you would load the customers (maybe only the id and name field) into an associated separate table (star schema)..

- Ralf

[mrooney]

Hi Ralph,

I need a loop because I also need, any given day of some selected period, some information about frequency of buying, avg. amount of purchas, avg. time beetween purchases, etc. There is no other way than a loop for calculations:  I need information about customer's behaviour in a defined period of time (mainly one year) from any actual date. I've not mentioned it before because I was trying to keep the issue as simpe as possible.

Regards.

[rbecher]

Ok, but why you do not load the customers in a separate table (or mapping table) outside / before the loop?

- Ralf

[mrooney]

Hi Ralph.

You are right. I do that and then the loop only makes the calculations over a period of time. I think there is not clear solution to this issue other than making  calculations into an specific object at execution time.

Regards.

[rbecher]

Don't understand why there would be an issue with the mapping if the mapping table was created once and complete outside the loop..

[mrooney]

Sorry, I don't think I've explained myself correctly. The loop is not an easy one. Here is a reduced version of it.

......................................

let vEndDate = Today()-1;

LET vLoopDate = $(vEndDate)-365;

.....................

................

do while vLoopDate<=vEndDate

........................

  RestaurantSegmentationGlobal:

  LOAD

  ........   // Loading new situation information to compare with prior situation

  ......

  FROM ------- (QVD);

  ........................

  MAP_REST_RFM_STATE:                               //That is Frequency - Recency - Monetary State

  Mapping LOAD [Restaurant Id]

         [Restaurant RFM Global Segment]

  From QVDs\TMP\RestaurantSegmentationPriorState.qvd (QVD);

  MAP_REST_VALORATION_STATE:

  Mapping LOAD [Restaurant Id],

         [Restaurant Valoration]

  From QVDs\TMP\RestaurantSegmentationPriorState.qvd (QVD);

......................

......................

// ACTUAL STATE CALCULATION

  M_REST_ACTUAL_STATE: // That is Activity State (Silver, Gold,....)

  Load [Restaurant Segmentation Country],

  [Restaurant Segmentation Location],

  [Restaurant Id],

...............................              

        Sum(if(ApplyMap('MAP_REST_RFM_STATE',[Restaurant Id])=1

        and ApplyMap('MAP_REST_VALORATION_STATE',[Restaurant Id])> 5

  and [Actual State]=0,1,0)) as [KPI Restaurant Bronze to Silver],

..............................

..............................

  Resident RestaurantSegmentationGlobal

  Group by [Restaurant Segmentation Country],

  [Restaurant Segmentation Location],

  [Restaurant Id],

  store M_REST_ACTUAL_STATE into QVDs\TMP\RestaurantSegmentationPriorState.qvd;

  ......................

  ...................

   let vLoopDate=$(vLoopDate)+1;

   loop

[rbecher]

I still presume a join would be better...

[rajeshvaswani77]

Ideally such data should be prepared and kept in the data warehouse, you should be reading the data from warehouse then from the reporting layer. This is just a suggestion. Would help if your design permits.

thanks,

Rajesh Vaswani