Problem with load script

[MarounSh1983]

hey guys, 

i am trying to figure out how to incremental load data to same qvd.

I am loading this script:

HAV_HATIVA_MAIN:
LOAD *
from [lib://עסקית/HATIVA_HAV/HAV_HATIVA.QVD](qvd);

LIB CONNECT TO 'DC_DWH';

HAV_HATIVA_NEW_DATA:

SQL SELECT "LOGICAL_DATE",
"SERVICE_ORDER_ID",
"ORDER_NUM",
"ORDER_SERIAL_NUM",
"SERVICE_ORDER_SOURCE_DESC",
"SERVICE_ORDER_TYPE_NAME",
"SERVICE_ORDER_STATUS_NAME",
"CUSTOMER_TYPE_NAME",
"SHEM_CHATIVA_BO",
"SERVICE_ORDER_CREATION_DATE",
"CUSTOMER_ID",
"DW_CUST_SUB_SEGMENT",
"TEUR_SHERUT_BI",
"MISSION_ID",
"NEW_VALUE_DESC",
"DATE_TECH",
"SHEM_MACHLAKA_BO"
FROM MAROUNSH."MS_PIPELINE_HAV_MAG";

Concatenate(HAV_HATIVA_MAIN)
LOAD *
Resident HAV_HATIVA_NEW_DATA;

drop table HAV_HATIVA_NEW_DATA;

store HAV_HATIVA_MAIN into [lib://עסקית/HATIVA_HAV/HAV_HATIVA.QVD](qvd);

I am getting this error:

The following error occurred:

Table 'HAV_HATIVA_NEW_DATA' not found

The error occurred here:

Concatenate(HAV_HATIVA_MAIN) LOAD * Resident HAV_HATIVA_NEW_DATA

**
1) HAV_HATIVA_MAIN table and the sql load has same column names.

2) when i load the SQL by itself, it does return data
**

any ideas?

[Oleg_Troyansky · Accepted solution]

Shalom haver,

This is one of the most common "catches" that any new developer goes through sooner or later. The code word is "automatic concatenation". Since the list of fields in your QVD file and in your database load is exactly the same, Qlik automatically concatenates the new data into the old data - whether you want it or not. 

In your case, this is the desired outcome, and you can simplify your script like this:

HAV_HATIVA_MAIN:
LOAD *
from [lib://עסקית/HATIVA_HAV/HAV_HATIVA.QVD](qvd);

LIB CONNECT TO 'DC_DWH';

SQL SELECT "LOGICAL_DATE",
"SERVICE_ORDER_ID",
"ORDER_NUM",
"ORDER_SERIAL_NUM",
"SERVICE_ORDER_SOURCE_DESC",
"SERVICE_ORDER_TYPE_NAME",
"SERVICE_ORDER_STATUS_NAME",
"CUSTOMER_TYPE_NAME",
"SHEM_CHATIVA_BO",
"SERVICE_ORDER_CREATION_DATE",
"CUSTOMER_ID",
"DW_CUST_SUB_SEGMENT",
"TEUR_SHERUT_BI",
"MISSION_ID",
"NEW_VALUE_DESC",
"DATE_TECH",
"SHEM_MACHLAKA_BO"
FROM MAROUNSH."MS_PIPELINE_HAV_MAG";

store HAV_HATIVA_MAIN into [lib://עסקית/HATIVA_HAV/HAV_HATIVA.QVD](qvd);

Personally, I like to be very explicit and specify where I want the new data to be concatenated to. That could look like this:

HAV_HATIVA_MAIN:
LOAD *
from [lib://עסקית/HATIVA_HAV/HAV_HATIVA.QVD](qvd);

LIB CONNECT TO 'DC_DWH';

CONCATENATE (HAV_HATIVA_MAIN)

LOAD *;   // Preceding load

SQL SELECT "LOGICAL_DATE",
"SERVICE_ORDER_ID",
"ORDER_NUM",
"ORDER_SERIAL_NUM",
"SERVICE_ORDER_SOURCE_DESC",
"SERVICE_ORDER_TYPE_NAME",
"SERVICE_ORDER_STATUS_NAME",
"CUSTOMER_TYPE_NAME",
"SHEM_CHATIVA_BO",
"SERVICE_ORDER_CREATION_DATE",
"CUSTOMER_ID",
"DW_CUST_SUB_SEGMENT",
"TEUR_SHERUT_BI",
"MISSION_ID",
"NEW_VALUE_DESC",
"DATE_TECH",
"SHEM_MACHLAKA_BO"
FROM MAROUNSH."MS_PIPELINE_HAV_MAG";

store HAV_HATIVA_MAIN into [lib://עסקית/HATIVA_HAV/HAV_HATIVA.QVD](qvd);

Now, if you didn't want to concatenate the data right away - for example, you might need to do some transformations first, then you could use the prefix NOCONCATENATE before your second LOAD.

As a side comment - I'm only assuming that the data in the database is only the new data, so you don't need to worry about selecting only the new or updated records, - because that's a completely different story.

Cheers,

[MarounSh1983]

Thanks a lot!