Incremental load in qliksense

[Aspiring_Developer]

Hello All,

I have below data set :-

V_MASTER:

LOAD REPORT_DATE, // snapshot date
LOB,
ISSUENO,
ISSUELOGGER,
ISSUERECEIVED,
ISSUELOGGED,
ISSUECLOSED,
STATUS,
SAC,
SAC_NAME,
SAC_BILLING_TEAM,
CONTACTNAME,
CONTACTNO,
OWNINGID,
OWNERNAME,
ENQCOM,
NEXTKCI,
UPDATES_COUNT,
LASTTOUCHED_DATE,
DIARYDATE,
EMAIL,
SOURCESYSTEM,
PRODUCT,
QUERYTYPE,
REASON_FOR_CONTACT,
CAUSE,
ADJ,
ADJAMOUNT,
DISPUTEDVAL,
REMINDME,
SOURCEACC,
FIRSTDESTINATION,
FIRST_TEAM,
ISSUE_RECD,
ISSUE_LOGGED,
OPEN_AGE_DAYS,
OPEN_AGE_BAND,
OPEN_WKG_DAYS,
OPEN_WKG_DAYS_BAND,
LOGGING_ADVISOR,
QUEUE,
UNALLOQUEUEID,
OWNINGADVISOR,
OWNING_SITE,
OWNING_TEAM,
OWNER,
OWNING_MGR,
APPVERSION,
LASTTOUCHED_WKG_DAYS,
LASTTOUCHED_WKG_DAYS_BAND,
PROGRESS,
NEXTINVOICEDATE,
MPADATE,
OBITEAM,
MANAGER,
MANAGEDBY,
INTEXT,
LASTNOTEINPUTBY,
LASTNOTEDATE,
LASTNOTE,
OPENATCOP,
TRANSFERMTH,
REGION,
CLOSINGID,
ISSUE_CLOSED,
CLOSED_AGE_DAYS,
CLOSED_AGE_BAND,
CLOSING_ADVISOR,
CLOSING_SITE,
CLOSING_TEAM,
CLOSER,
CLOSING_MGR,
COPYBILLS,
MEDIUM,
ISSUETYPE,
RAG_STATUS,
SUMMARY,
QUEUENAME,
CUSTOMER_NAME,
ACCOUNT_NUMBER,
COMPANY_NAME,
ASSIGNEE,
REPORTER,
CATEGORY,
COMPONENT,
REQUESTED_DATE,
CREATED_DATE,
UPDATED,
RESOLVED,
CYCLE_TIME,
RESOLUTION,
DAYS_SINCE_LAST_COMMENT,
ISSUE_LINKS,
VOTES,
START_DATE,
REASON,
WORK_COMMENCED_DATE,
END_DATE,
COMPANY_NAME2,
ACCOUNT_NUMBER2,
QUERY_TYPE2,
CUSTOMER_REQUESTED_DATE2,
CONSEQUENCE,
FIRST_RESPONSE_DATE,
LAST_UPDATER,
PARTICIPANTS,
TRIGGER,
VULNERABILITY,
INVOICE_NUMBER,
INVOICE_NUMBER2,
LAST_NAME,
ORIGINAL_ESTIMATE,
ITEM_COUNT,
CHASE_DATE,
RECEIVED_FROM,
COUNTRY,
AMOUNT,
QUERY_ANALYSIS,
FILE_DATE,
AVG_LOGGED_TIME,
AVG_LOGGED_TIME_BAND,
CLOSED_WEEK_COMM,
CLOSED_WEEK_ENDING,
MTH,
COUNT,
ISSUE_CLOSER,
SEGMENTATION

From....

Now , I need to apply incremental load on the basis of these 3 fields:-

I want to achieve below :-

1) When the data for new date comes for ex- if we receive data for today's date , it should first delete all the records where STATUS= 'OPEN' AND 'UNALLOCATED' 

2) It should check that only unique issue no are updated and there should be no duplicate records.

Initially I tried to apply incremental on issue no but it didn't worked . So , now I have to apply it on date . I can do it for date but don't know how to deleted the records and also to make sure unique issue no are updated.

Can anyone please help ?

Thanks in advance

[rubenmarin]

HI, if you load first the issues from db, you can delte it from qvd suing Exists(), like:

// Load from DB
DataTable:
LOAD ISSUENO,...;
SELECT ISSUENO...;

// Add Data from qvd
Concatenate (DataTable)
LOAD ISSUENO...
FROM [qvd]
Where not Exists(ISSUENO);

Using this you only keep the issue with the last startus of the qvd.

If in db can be more than reord for each isseno, each one with it's status and report date, and you want to keep the last status by report date, just do a load sortde by this date descendant and the 'not Exists()' will keep only the first version loaded of each issue.

[Aspiring_Developer]

Hi @rubenmarin 

Thanks for your response.

How should I deleted the records having status as open and unallocated . I need to remove them every time the new data comes in .

[rubenmarin]

Hi, using where not exists. Have you tried what I posted before?