topic Mapping Load doesn't work within a loop? in QlikView

Mapping Load doesn't work within a loop?

mrooney — Sun, 01 Sep 2013 08:58:05 GMT

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.

Re: Mapping Load doesn't work within a loop?

ariel_klien — Sun, 01 Sep 2013 09:28:31 GMT

can you send us the script?

Re: Mapping Load doesn't work within a loop?

rbecher — Sun, 01 Sep 2013 10:27:06 GMT

Hi,

maybe it's better to use a join instead?

- Ralf

Re: Mapping Load doesn't work within a loop?

Anonymous — Sun, 01 Sep 2013 12:27:38 GMT

Miguel,

I assume you have something like this:

FOR n = 1 to 100

Map:

MAPPING LOAD

...

data:

LOAD

applymap('Map'...)...

RESIDENT

I suspect that QlikView is creating maps 'Map-1', 'Map-2', etc. Try this, so you explicitly create map names, and refer to them in the applymap:

FOR n = 1 to 100

Map$(n):

MAPPING LOAD

...

data:

LOAD

applymap('Map$(n)'...)...

RESIDENT

Regards,

Michael

Re: Mapping Load doesn't work within a loop?

Oleg_Troyansky — Sun, 01 Sep 2013 16:46:47 GMT

Very clever solution, @Michael!

I don't think that QlikView creates Map-1, Map-2, etc... It continues to build up the same mapping table. I see 2 possible issues:

1. If the mapping values aren't changing from one iteration of the loop to another, then the issue is in duplicate values. I'd recommend to simply add DISTINCT

2. If the mapping values should change from one iteration to another, then the old values need to be discarded, and Michael's solution with numbering the Map is perfect!

cheers,

Oleg Troyansky

Re: Mapping Load doesn't work within a loop?

rbecher — Sun, 01 Sep 2013 17:07:22 GMT

..then you would need a dynamical nesting ApplyMap(), not a very realistic approach.

- Ralf

Re: Mapping Load doesn't work within a loop?

mrooney — Sun, 01 Sep 2013 20:11:07 GMT

Hi Michael,

I agree with Oleg. Your solutions works fine. I tried it before. But I just see a slight problem (or its seems to me it could be), I've got a huge amount of records, so worried about loading so many tables in memory. In case I have to make a loop for a whole Year I would be creating 365 table of more than 2 millions records each. My problem is performance in load time (every 12 hours).

I've been thinking about partial (incremental) reloads in order to release memory after, lets say, 50 loops each.

But I'm still trying to find a work-around.

Regards.

Re: Mapping Load doesn't work within a loop?

mrooney — Sun, 01 Sep 2013 20:13:25 GMT

Hi Ralf,

I've already tried with a join. But no luck. Poor performance.

Regards

Re: Mapping Load doesn't work within a loop?

mrooney — Sun, 01 Sep 2013 20:20:26 GMT

Hi Ariel, the code is pretty much like the one Michael wrote.

Regards.

Re: Mapping Load doesn't work within a loop?

Anonymous — Mon, 02 Sep 2013 01:53:52 GMT

"I'd recommend to simply add DISTINCT"

Hi Oleg,

Good point, thanks. In fact, I always use "distinct" with mapping load, unless it is inline. Somehow skipped it in the example above...

Regards,

Michael

Re: Mapping Load doesn't work within a loop?

rbecher — Mon, 02 Sep 2013 05:40:46 GMT

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

Re: Mapping Load doesn't work within a loop?

mrooney — Tue, 03 Sep 2013 07:53:41 GMT

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.

Re: Mapping Load doesn't work within a loop?

rbecher — Tue, 03 Sep 2013 08:16:20 GMT

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

- Ralf

Re: Mapping Load doesn't work within a loop?

mrooney — Tue, 03 Sep 2013 08:24:38 GMT

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.

Re: Mapping Load doesn't work within a loop?

rbecher — Tue, 03 Sep 2013 08:41:40 GMT

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

Re: Mapping Load doesn't work within a loop?

mrooney — Tue, 03 Sep 2013 09:22:07 GMT

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

Re: Mapping Load doesn't work within a loop?

rbecher — Tue, 03 Sep 2013 10:03:17 GMT

I still presume a join would be better...

Re: Mapping Load doesn't work within a loop?

rajeshvaswani77 — Tue, 03 Sep 2013 10:07:09 GMT

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