Incremental Load Help

[cbaqir]

I am going out of my mind trying to get this to work. Someone please help. I followed a How-to on incremental loads but had some issues with it oob.

I have a one time spreadsheet that I create a QVD with that contains data from 6/16-5/17.

I then will have two files every month that I want to append to this QVD. I am working on the 6/17 file and I just can't get it to work. I believe the issues are around the keys created that are needed to join to other tables but I can't figure out what I am doing wrong.

Can someone please help? The spreadsheets for 6/17 are attached.

My initial QVD code:

PP_USAGE:
LOAD TRX_DATE,
AutoNumber( VERSION_PW_CAT_ID) as %PP_Key,
VERSION_PW_CAT_ID,
   FACILITY,
   DOMAIN,
AutoNumber( VERSION_PW_CAT_ID) & '|' & DOMAIN as %PP_DOMAIN_Key, //Join to ORD_USAGE
   USAGE as CNT_USAGE
FROM
[..\External_Data\PP_USAGE_ECISA.xlsx]
(ooxml, embedded labels, table is CCG_PLANS);

  //CB 9/18/17 Store initial load as QVD
store PP_USAGE into ..\QVD\01_Extract_QVD\PP_USAGE.qvd;

[rwunderlich]

As a general starting point:  You should not AutoNumber fields to be stored in QVDs and added to later. The reason is that the next set of source files you load will restart the AutoNumber() sequence, making incorrect associations.

-Rob

[vkish16161]

rwunderlich‌ Is right.

You could use hash functions for storing QVDs.

As far as Incremental issue is concerned try doing it on a date field with where not exists clause.

Check if your date field is a time stamp.

[cbaqir]

I did not know that but it makes sense. I needed a way to create a unique identifier. Any other suggestions on how to do that and still use QVD and incremental load?

[cbaqir]

I will look into a hash function, thanks.

I am using this for the date field:

NEW_USAGE_ECISA:
LOAD TRX_DATE,
// VERSION_PW_CAT_ID,
   // DESCRIPTION,
   FACILITY,
// DOMAIN,
   autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %PP_DOMAIN_Key,
// DESCRIPTION & '_' & DOMAIN as %DESC_DOMAIN_Key, //join to Regional Variability
   USAGE as  CNT_USAGE
// EXTRACT_DATE
FROM
[..\External_Data\1DHO_EKG_PLAN_USAGE_ECISA.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where TRX_DATE > $(Maxdate);