Mapping load takes too much time to reload

[kishorj1982]

Hi All,

In my script mapping load takes too much time to reload.

DIRECTORY $(RootFolderForInputQVD3);

// Retrieves the latest actual goods issue date for sales document item
[Actual Goods Issue Date Map]:
//first 1000
MAPPING LOAD [Sales Document Item Key],
Date (Max ([Actual Goods Issue Date])) as [Actual Goods Issue Date]
FROM
[SalesOrderDeliverySSL.qvd]
(qvd)
// Do not retrieve deleted lines
WHERE [Order Schedule Line Delivery Deleted] <> 'X'
GROUP BY [Sales Document Item Key];

DIRECTORY $(RootFolderForInputQVD);

// Retrieves the OTCC date for sales document item
[First OTCC Date Map]:
//first 1000
MAPPING LOAD [Sales Document Item Key],
Date (Min ([First OTCC Date])) as [First OTCC Date]
FROM
[ItemStatus.qvd] (qvd)
WHERE Not IsNull ([First OTCC Date])
GROUP BY [Sales Document Item Key];

[Item Category Map]:
MAPPING LOAD [Sales Document Item Key],
[%Item category_ITEM_CATEG]
FROM
[SalesOrder.qvd] (qvd)
// Do not retrieve offers (sales document types ZQT and ZQH)
WHERE Match ([%Sales doc. type_DOC_TYPE], 'ZQT', 'ZQH') = 0;

// Retrieves the latest goods receipt date for sales document item
[Goods Receipt Date Map]: //Required
//first 1000
MAPPING LOAD [Sales Document Item Key],
Date (Max ([Goods Receipt Date])) as [Goods Receipt Date]
FROM
[ScheduleLine.qvd] (qvd)
GROUP BY [Sales Document Item Key];

Could you please suggest how to get rid of this.

Thanks

[marcus_sommer]

It's not the mapping itself which caused the rather long load-times else that the loadings are aggregation-loads with where-clauses. This means you need to look if they could be optimized. For this you could apply a multi-step approach like this:

dummy: load 'X' as [Order Schedule Line Delivery Deleted] autogenerate 1;

temp: load [Sales Document Item Key], [Actual Goods Issue Date]
from [SalesOrderDeliverySSL.qvd] (qvd) where not exists([Order Schedule Line Delivery Deleted]);

[Actual Goods Issue Date Map]:
MAPPING LOAD
    [Sales Document Item Key],
    Date (Max ([Actual Goods Issue Date])) as [Actual Goods Issue Date]
resident temp GROUP BY [Sales Document Item Key];

drop tables dummy, temp;

Depending on your data the above used not exists() may not completly fit - then you could use your origin where-clause with: WHERE [Order Schedule Line Delivery Deleted] <> 'X' but you will loose the optimized load of the qvd.

Therefore it might be worth to take a few additionally steps to ensure that you could apply such logic. For example if you after the creation of your SalesOrderDeliverySSL table makes a run through the fieldvalues of your condition-field and stores it too as qvd - and then you could create with it the above mentioned dummy-table (then as a white-listing and not as a black-listing).

A further approach would be to implement incremental load-approaches. Within the second half of the following posting you will find a lot of examples for incremental loadings and using exists() to keep the loadings optimized:

Advanced-topics-for-creating-a-qlik-datamodel

- Marcus