Problem in building single Key from multiple QVDs

[#1]

Hi,

I have

product data - QVD,

shipping table (all ONLY ACTIVE products offers millions of records) - QVD,

merchant table - QVD

Order Data (All ACTIVE and DEACTIVATED products orders) - New information to add.

(please refer to diagram attached.

I want output in such a manner that if any order for that product (exporting and importing country wise) is placed, then it should populate orders against it. OR prompt (NO Orders)

Similarly is a product is de-activated and there were orders, table should have that product.

I was looking to built a key product_id & export_country & country to match (outer join) but due to multiple QVDs I am not sure how to proceed.

Could you please help?

Desired Result:

Present Data model:

Present Detailed Load Script:

shipping_dim:

LOAD schema_type, shipping_costs_sk,

     product_sk as shp_product_sk,

     product_id,

     currency as cur_currency,

     country,

     country as las_country,

     region,

     net_retail_price,

     shipping_cost,

     other_cost,

     vat_cost,

     customer_cost

FROM

E:\QVDs\QVDs\shipping_dim.qvd

(qvd)

;

product_dim:

LOAD `product_sk`,

    `merchant_sk`,

    `brand_name`,

    `product_id`,

    `product_name`,

    `redemption_type`,

    `product_type`,

    `is_active`,

    `category_name`,

    discount,

    release_date;

SQL SELECT *

FROM `ll_etl`.`vdc_ll_product_dim`;

join (product_dim)

LOAD merchant_sk,

     merchant_id,

     merchant_name,

     merchant_type,

     merchant_currency,

     team,

     exporting_country,

     region as mer_region,

     network_id,

     network_name,

     merchant_status

FROM

E:\QVDs\QVDs\merchant_dim.qvd

(qvd);

New Table to add:

odr_data:

LOAD `program_code`,

    `product_id` ,

  country ,

    `exporting_country` ,

    date,

    `year_no`,

    `month_display`,

    `month_no`,

    `redemption_type`,

    `promo_order_flag`,

    orders,

    `turnover_eur`,

    `turnover_usd`,

    `ordered_products`,

    `ll_profit`,

    `ll_profit_usd`,

    `cc_fees`;

SQL SELECT *

FROM `ll_etl`.`vdc_ll_last_2_yrs`;

Thank you in Advance for your help!

Many Thanks and Regards

- Deep

[sasikanth]

HI

Do a Left join between

Shipping and Orders

then try to map with the product_dim

[#3]

HI Sasikanth,

Thank you for your help but I am looking for a combination of product id + export country + country.

If I join shipping and Orders I will have to join on product id + country (export country will be missing)

Please suggest.

Thanks and Regards

- Deep

[stevedark]

I'm not sure I fully follow what the problem is here, but if I have understood correctly, you can build a composite key on both the Shipping Dim and Order Data tables.  Simply do string concatenations, eg:

product_id & ':' & export_country & ':' & country as ProductCountryKey,

Ideally do this as you create QVD's, rather than when loading from them so that optimised loads from QVD can be performed.

In general it is best not to use the JOIN statement in your QlikView load, unless you have a good reason and you have a clear understanding of the consequences.  Build keys and then remove fields that are no longer needed.

If the Shipping table has all possible ProductCountryKey variations this is quite straight forward, have the key and all three fields in the shipping table in your end model and just the ProductCountryKey in the Order Data table.  If it is possible that there are keys in the Order that don't exist in the Shipping table you will need to create a Link Table.  This needs to be created with the fields ProductCountryKey, product_id, export_country and country, and needs to be created from a RESIDENT load from both tables.  You then need to explicitly DROP FIELD all of the non key fields from both tables (as they will be found in the Link Table).

Your expression will then be:

if(sum(orders) = 0, 'No Orders', sum(orders))

Hope that all makes sense.

Steve

[#5]

Hi Steve,

Thank You for your comments.

Trouble here is:

1. Making a Key from multiple fields loaded from more than 1 QVDs

(shipping dim have partially ProductCountryKey (export country is missing which get load from another QVD i.e. merchant_dim))

Composite key I am looking to make is product_id, export_country and country (which is present in Order Table)

Please suggest to encounter this problem.

Regards

- Deep

[stevedark]

Perhaps you could map in the extra key using an ApplyMap?  Or, as the underlying data store is a RDBMS you could bring the data across in a JOIN.  Try to avoid duplicating large string values by using SQL Joins (this is why ApplyMap is generally preferable).

Steve