Incremental load in qliksense

Aspiring_Developer — Fri, 29 Jul 2022 08:01:24 GMT

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

Re: Incremental load in qliksense

rubenmarin — Sat, 30 Jul 2022 07:18:29 GMT

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.

Re: Incremental load in qliksense

Aspiring_Developer — Sat, 30 Jul 2022 07:55:33 GMT

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 .

Re: Incremental load in qliksense

rubenmarin — Sat, 30 Jul 2022 10:11:59 GMT

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

topic Re: Incremental load in qliksense in App Development

Incremental load in qliksense

Re: Incremental load in qliksense

Re: Incremental load in qliksense

Re: Incremental load in qliksense