Solved: Mapping Load verse JOINs verse ? - Qlik Community

Report Inappropriate Content · ‎2010-10-21

<body>In the script below, I INNER JOIN a fact table to three dimensional tables. The reason I perform an INNER JOIN is so that I can limit the data in the fact table by using fields in the dimensional tables. My main concern is whether or not this is the best way to achieve my goal. I have been trying to research Mapping Loads since it was suggested to me that using a Mapping Load may work better. However, I am confused on how Mapping Loads work and whether they would allow me to achieve my goals. My questions are: (1) Would use of a Mapping Load be a more efficient way to limit my data? (2) If the answer to my first question is yes, than how do I use a Mapping Load in my script? Any assistance is greatly appreciated. Thanks, Godfrey Script Below: // First import is the Customer Dimensional table <st1:stockticker>DATA</st1:stockticker>: LOAD SHIP_TO_CODE, BUSINESS_SEGMENT_CODE, SALES_<st1:stockticker>ORG</st1:stockticker> FROM .. \<st1:stockticker>DATA</st1:stockticker>\QVD\DimCustomer.QVD (qvd) WHERE( BUSINESS_SEGMENT_CODE ='$(vCustBusSegment)' <st1:stockticker>AND</st1:stockticker> SALES_<st1:stockticker>ORG</st1:stockticker> = '$(vSalesOrg)'); // Second import is the Data/Transactional table INNER JOIN (<st1:stockticker>DATA</st1:stockticker>) LOAD SALE_ORDER_NR, FINAL_GI_<st1:stockticker>ACTL</st1:stockticker>_DT_CD AS <st1:stockticker>DAY</st1:stockticker>_CODE, SOH_SHIP_COND_CD, COUNT_ORDER, SHIP_TO_CODE, FROM .. \<st1:stockticker>DATA</st1:stockticker>\QVD\ODSDrvdSAPSalesOrderHdr_ETL.QVD (qvd) WHERE( FINAL_GI_<st1:stockticker>ACTL</st1:stockticker>_DT_CD >= '$(vYearStart)'); // Third import is the Calendar Dimensional table INNER JOIN (<st1:stockticker>DATA</st1:stockticker>) LOAD <st1:stockticker>DAY</st1:stockticker>_CODE, WEEK_CODE, PERIOD_CODE, QTR_CODE, HYR_CODE, YR_CODE FROM .. \<st1:stockticker>DATA</st1:stockticker>\QVD\DimDailyCalendar.QVD (qvd); // Fourth import is the Shipping Condition Dimensional table INNER JOIN (<st1:stockticker>DATA</st1:stockticker>) LOAD <st1:stockticker>SAP</st1:stockticker>_<st1:stockticker>DATA</st1:stockticker>_CODE AS SOH_SHIP_COND_CD, <st1:stockticker>SAP</st1:stockticker>_<st1:stockticker>DATA</st1:stockticker>_DESCRIPTION AS SOH_SHIP_COND_DESC FROM .. \<st1:stockticker>DATA</st1:stockticker>\QVD\VLKPSAPShippingCondition.QVD (qvd);</body>

Miguel_Angel_Baeyens · ‎2010-10-21

Hello Godfrey,

I don't know if loading this or that way is going to be faster before trying myself.

I do use some applymaps in where clauses to reduce the number of records loaded depending on several criteria, take the following example: I have got to load data from several companies every day depending on their daily revenues stored in a QVD file. The reload process is unattended, and scheduled on the server, and I cannot do it manually in the script, since I don't know which companies I have got to check.

CompaniesToBeCheckedMap:MAPPING LOAD CompID, TotalDailyRevenueFROM COMPANIES.QVD (qvd)WHERE 1=1; // Needed since a mapping is not supported for optimized qvd loads Data:LOAD CompID, Field1, Field2FROM FILE.QVD (qvd)WHERE ApplyMap('CompaniesToBeCheckedMap', CompID) > 2000;

I know the example is pretty simple but it may help you to understand (and try, if that suits you) applymap in loading statements.

Hope that helps

View solution in original post

Miguel_Angel_Baeyens · ‎2010-10-21

Hello Godfrey,

I don't know if loading this or that way is going to be faster before trying myself.

I do use some applymaps in where clauses to reduce the number of records loaded depending on several criteria, take the following example: I have got to load data from several companies every day depending on their daily revenues stored in a QVD file. The reload process is unattended, and scheduled on the server, and I cannot do it manually in the script, since I don't know which companies I have got to check.

CompaniesToBeCheckedMap:MAPPING LOAD CompID, TotalDailyRevenueFROM COMPANIES.QVD (qvd)WHERE 1=1; // Needed since a mapping is not supported for optimized qvd loads Data:LOAD CompID, Field1, Field2FROM FILE.QVD (qvd)WHERE ApplyMap('CompaniesToBeCheckedMap', CompID) > 2000;

I know the example is pretty simple but it may help you to understand (and try, if that suits you) applymap in loading statements.

Hope that helps

Report Inappropriate Content · ‎2010-10-26

Miguel,

I appreciate your assistance. I was able to use your example to create multiple MAPPING LOAD / APPLY MAP scenario that achieves my goals.

Thanks again,

Godfrey