Accepted Solutions
These are some random improvement notes:
- You are performing several transformations applied to the original QVD (DTM_COMMERCIAL_LINE_Facts.qvd) that should be done as part of its generation, I am talking about these transformations:
- If your fact table is small the following enhancements might not show any improvements
- When building the table: tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE
NUM(YEAR(DateNum), '0000') & NUM(MONTH(DateNum), '00') AS MONTH,
NUM(Year(DateNum), '0000') & NUM(Ceil(Month(DateNum)/3), '00') AS QUARTER,
NUM(YEAR(DateNum), '0000') & NUM(WEEK(DateNum), '00') AS WEEK,
NUM(YEAR(DateNum), '0000') AS YEAR,
The functions NUM, YEAR, MONTH, WEEK and Ceil applied over thousands of records may have a significant impact in the script performance (running time).
The fields: MONTH, QUARTER, WEEK and YEAR are likely to be consume across many dashboards and applications, so creating them once will simplify the overall applications' QVDs.
ApplyMap('MAP_SALES_REP',TERRITORY_CODE , ' NOT FOUND') AS SALES_REP,
ApplyMap('Map_TERRITORY',TERRITORY_CODE , ' NOT FOUND') AS SALES_TERRITORY,
ApplyMap('Map_CARRIER',CARRIER_CODE, ' NOT FOUND') AS CARRIER_CODE,
ApplyMap('MAP_SALES_GROUP',TERRITORY_CODE, ' NOT FOUND') AS SALES_GROUP,
ApplyMap('MAP_COMMODITY',JOB_REFERENCE, ' NOT FOUND') AS COMMODITY_CODE
The same concern with these ApplyMap functions, nothing prevent you to do them here, but, it will be better to perform them while creating the QVD: DTM_COMMERCIAL_LINE_Facts.qvd
The DIRECTION field, it is a heavy combination of IF/LEN/TRIM/string comparisons, specially if you are dealing with a large fact table; Also, DIRECTION should be an integer value, like 0 and 1 instead of 'IMPORT' and 'EXPORT', e.g. ( 0 = Import, 1 = Export ) Qlik as many other language are more efficient comparing/handling integers instead of strings!
The DIRECTION field (as numeric) should be part of the Facts qvd; the UI interface could easily map 0 and 1 to text when required.
These condition's expressions applied over thousands of records take time:
LEN(TRIM(ORIGIN_COUNTRY_CODE)) = 0 OR ORIGIN_COUNTRY_CODE='NULL' OR ORIGIN_COUNTRY_CODE='-'
LEN(TRIM(DESTINATION_COUNTRY_CODE)) = 0 OR DESTINATION_COUNTRY_CODE='NULL' OR DESTINATION_COUNTRY_CODE='-'
POL_COUNTRY = 'UNITED STATES' OR POL_COUNTRY = 'CANADA'
POD_COUNTRY = 'UNITED STATES' OR POD_COUNTRY = 'CANADA'
ORIGIN_COUNTRY_CODE = 'US' OR ORIGIN_COUNTRY_CODE = 'CA'
DESTINATION_COUNTRY_CODE = 'US' OR DESTINATION_COUNTRY_CODE = 'CA'
The expressions checking if the fields ORIGIN_COUNTRY_CODE and DESTINATION_COUNTRY_CODE are not nulls should be part of the Fact table, I will introduce two fields there: Valid_Origin_Country and Valid_Destination_Country with 1/0 values for Yes/No
Your fact table records four different interpretation of COUNTRIES, with the prefixes; POL_, POD_, ORIGIN_ and DESTINATION_ and knowing if they identify North America (UNITED STATES/CANADA, or US/CA) justify the introduction of four FLAGs in your fact table with these names: POL_NorthAmerica, POD_NorthAmerica, ORIGIN_NorthAmerica and DESTINATION_NorthAmerica, each one of them containing 1/0 values for Yes/No; these fields should be part of the FACT table.
The KEY_COMMODITY and SUB_KEY_COMMODITY fields rely in heavy IF statements, perhaps they could be replaced with a ApplyMap; but I withhold further comments on these two fields, pending your reply and implementation of these changes:
SUMMARY:
- The source FACT table: DTM_COMMERCIAL_LINE_Facts.qvd should contain several of the fields this load script is creating.
- The ApplyMap should take place at the time of creating the fact table.
- The DIRECTION fields should be part of the fact table.
- The resolution of the _NorthAmerica transactions should take place when building the Fact table, and appear as columns in it.
- Use flag fields, e.g. 0/1 instead of text-strings
- I did not comment on the LEFT joins, I am waiting for your reply to resume my suggestions by including the LEFT joins.
Please, confirm if you will implement these enhancements, and if you do, share with us the time the script is taking.
hth
Hi @Luben
I don't have any issue with the KEY_COMMODITY and SUB_KEY_COMMODITY fields, perhaps my comments were regarding improving the script performance.
Ideally, the changes that I suggested were to be applied to the Load Script creating the initial FACTs qvd, my understanding is that you are not allowed to touch that load script. Which is not a problem.
Also, i thought that the script you posted was part of a new solution or dashboard, and you were adding columns expected by that solution/dashboard.
Now, this diagram shows a proposed idea to implement the suggestion above and enhancing the FACTs.qvd.
Now, I am attaching a QVF file with these enhancements implemented in the load script;
Hi @Luben
The diagram below is a top-level view of your Load Script, I will reference this diagram with my suggestions.
NOTE: It will be good that you find and upload a recent log file generated by this load script, it will be extremely helpful
hth
These are some random improvement notes:
- You are performing several transformations applied to the original QVD (DTM_COMMERCIAL_LINE_Facts.qvd) that should be done as part of its generation, I am talking about these transformations:
- If your fact table is small the following enhancements might not show any improvements
- When building the table: tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE
NUM(YEAR(DateNum), '0000') & NUM(MONTH(DateNum), '00') AS MONTH,
NUM(Year(DateNum), '0000') & NUM(Ceil(Month(DateNum)/3), '00') AS QUARTER,
NUM(YEAR(DateNum), '0000') & NUM(WEEK(DateNum), '00') AS WEEK,
NUM(YEAR(DateNum), '0000') AS YEAR,
The functions NUM, YEAR, MONTH, WEEK and Ceil applied over thousands of records may have a significant impact in the script performance (running time).
The fields: MONTH, QUARTER, WEEK and YEAR are likely to be consume across many dashboards and applications, so creating them once will simplify the overall applications' QVDs.
ApplyMap('MAP_SALES_REP',TERRITORY_CODE , ' NOT FOUND') AS SALES_REP,
ApplyMap('Map_TERRITORY',TERRITORY_CODE , ' NOT FOUND') AS SALES_TERRITORY,
ApplyMap('Map_CARRIER',CARRIER_CODE, ' NOT FOUND') AS CARRIER_CODE,
ApplyMap('MAP_SALES_GROUP',TERRITORY_CODE, ' NOT FOUND') AS SALES_GROUP,
ApplyMap('MAP_COMMODITY',JOB_REFERENCE, ' NOT FOUND') AS COMMODITY_CODE
The same concern with these ApplyMap functions, nothing prevent you to do them here, but, it will be better to perform them while creating the QVD: DTM_COMMERCIAL_LINE_Facts.qvd
The DIRECTION field, it is a heavy combination of IF/LEN/TRIM/string comparisons, specially if you are dealing with a large fact table; Also, DIRECTION should be an integer value, like 0 and 1 instead of 'IMPORT' and 'EXPORT', e.g. ( 0 = Import, 1 = Export ) Qlik as many other language are more efficient comparing/handling integers instead of strings!
The DIRECTION field (as numeric) should be part of the Facts qvd; the UI interface could easily map 0 and 1 to text when required.
These condition's expressions applied over thousands of records take time:
LEN(TRIM(ORIGIN_COUNTRY_CODE)) = 0 OR ORIGIN_COUNTRY_CODE='NULL' OR ORIGIN_COUNTRY_CODE='-'
LEN(TRIM(DESTINATION_COUNTRY_CODE)) = 0 OR DESTINATION_COUNTRY_CODE='NULL' OR DESTINATION_COUNTRY_CODE='-'
POL_COUNTRY = 'UNITED STATES' OR POL_COUNTRY = 'CANADA'
POD_COUNTRY = 'UNITED STATES' OR POD_COUNTRY = 'CANADA'
ORIGIN_COUNTRY_CODE = 'US' OR ORIGIN_COUNTRY_CODE = 'CA'
DESTINATION_COUNTRY_CODE = 'US' OR DESTINATION_COUNTRY_CODE = 'CA'
The expressions checking if the fields ORIGIN_COUNTRY_CODE and DESTINATION_COUNTRY_CODE are not nulls should be part of the Fact table, I will introduce two fields there: Valid_Origin_Country and Valid_Destination_Country with 1/0 values for Yes/No
Your fact table records four different interpretation of COUNTRIES, with the prefixes; POL_, POD_, ORIGIN_ and DESTINATION_ and knowing if they identify North America (UNITED STATES/CANADA, or US/CA) justify the introduction of four FLAGs in your fact table with these names: POL_NorthAmerica, POD_NorthAmerica, ORIGIN_NorthAmerica and DESTINATION_NorthAmerica, each one of them containing 1/0 values for Yes/No; these fields should be part of the FACT table.
The KEY_COMMODITY and SUB_KEY_COMMODITY fields rely in heavy IF statements, perhaps they could be replaced with a ApplyMap; but I withhold further comments on these two fields, pending your reply and implementation of these changes:
SUMMARY:
- The source FACT table: DTM_COMMERCIAL_LINE_Facts.qvd should contain several of the fields this load script is creating.
- The ApplyMap should take place at the time of creating the fact table.
- The DIRECTION fields should be part of the fact table.
- The resolution of the _NorthAmerica transactions should take place when building the Fact table, and appear as columns in it.
- Use flag fields, e.g. 0/1 instead of text-strings
- I did not comment on the LEFT joins, I am waiting for your reply to resume my suggestions by including the LEFT joins.
Please, confirm if you will implement these enhancements, and if you do, share with us the time the script is taking.
hth
for both direction The KEY_COMMODITY and SUB_KEY_COMMODITY fields are logic that was given by business.
what would be the best way to write the If statements.
Also the DTM_COMMERCIAL_LINE_Facts.qvd was already created i am just adding those fields to them with the apply map function.
Hi @Luben
I don't have any issue with the KEY_COMMODITY and SUB_KEY_COMMODITY fields, perhaps my comments were regarding improving the script performance.
Ideally, the changes that I suggested were to be applied to the Load Script creating the initial FACTs qvd, my understanding is that you are not allowed to touch that load script. Which is not a problem.
Also, i thought that the script you posted was part of a new solution or dashboard, and you were adding columns expected by that solution/dashboard.
Now, this diagram shows a proposed idea to implement the suggestion above and enhancing the FACTs.qvd.
Now, I am attaching a QVF file with these enhancements implemented in the load script;
