Left Keep not working for all data

Tue, 05 May 2015 10:30:26 GMT

I have two tables in separate QVD files. However, when I load them into my script and do a Left Keep to link the tables, some of the data from the right table is not being shown. It is inconsistent as it shows some of the data as I want it to be. I actually checked the raw data files to see if there was any spacing and there isn't any.

Would anyone be able to help?

Re: Left Keep not working for all data

Gysbert_Wassenaar — Tue, 05 May 2015 15:53:40 GMT

That's what Left Keep does. It removes any records in the right side table that don't have matching records in the left side table. If you don't want that to happen, but want to keep all the data of the right side table then don't use Left Keep. Just leave it out and your tables will be linked without removing data.

Re: Left Keep not working for all data

Tue, 05 May 2015 15:59:21 GMT

I think what has happened is that I had a loop to go through a set of txt files to store all the data in a single QVD file and it looks like not all the information is coming through properly.

This is what I am using:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET vDataFolder = 'C:\Users\Desktop\Work Documents\QlikView\Order Data';
SET vQVDFolder = 'C:\Users\Desktop\Work Documents\QlikView\QVD Files\OrderData.qvd';

FOR EACH vFile IN FileList('$(vDataFolder)\*txt');

ORDER_DATA:
LOAD FileName() as Order_File_Name,
Replace(FileName(),'.txt','') as Order_Period,
[Order-id] as Order_Order_ID,
[merchant-order-id] as Order_Merchant_Order_ID,
[purchase-date] as Order_Purchase_Date,
[last-updated-date] as Order_Last_Updated_Date,
[order-status] as Order_Order_Status,
[fulfillment-channel] as Order_Fulfillment_Channel,
[sales-channel] as Order_Sales_Channel,
[order-channel] as Order_Order_Channel,
url as Order_URL,
[ship-service-level] as Order_Ship_Service_Level,
[product-name] as Order_Product_Name,
sku as Order_SKU,
[item-status] as Order_Item_Status,
quantity as Order_Quantity,
currency as Order_Currency,
[item-price] as Order_Item_Price,
[item-tax] as Order_Item_Tax,
[shipping-price] as Order_Shipping_Price,
[shipping-tax] as Order_Shipping_Tax,
[gift-wrap-price] as Order_Gift_Wrap_Price,
[gift-wrap-tax] as Order_Gift_Wrap_Tax,
[item-promotion-discount] as Order_Item_Promotion_Discount,
[ship-promotion-discount] as Order_Ship_Promotion_Discount,
[ship-city] as Order_Ship_City,
[ship-state] as Order_Ship_State,
[ship-postal-code] as Order_Ship_Postal_Code,
[ship-country] as Order_Ship_Country,
IF(WildMatch([ship-country],'AT','BE', 'BG', 'CY', 'CZ', 'DK', 'EE', 'FI', 'FR', 'DE', 'GR', 'HU', 'IE', 'IT', 'LV', 'LT', 'LU', 'MT', 'NL', 'PL', 'PT', 'RO', 'SK', 'SI', 'ES', 'SE', 'GB', 'HR')>=1,'EU',IF([ship-country]='','','Non-EU')) as Order_EU_Identifier,
[promotion-ids] as Order_Promotion_IDs
FROM
$(vFile)
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

STORE ORDER_DATA INTO $(vQVDFolder) (qvd);

NEXT;

DROP TABLE ORDER_DATA;

Re: Left Keep not working for all data

Tue, 05 May 2015 16:19:31 GMT

I've got it to work now - it was uploading the data partially - I replaced the 'msq' with 'no quotes':

A previous post has helped me fix this:

Issue importing a txt file

topic Re: Left Keep not working for all data in QlikView

Left Keep not working for all data

Re: Left Keep not working for all data

Re: Left Keep not working for all data

Re: Left Keep not working for all data