topic Re: oracle move in QlikView

oracle move

raadwiptec — Fri, 06 Nov 2015 11:58:50 GMT

hi all,

This is my scenario.I have 2 tables with the same below structure. everymonth I receive a file to load this custtotnew table. But before loading the custtotnew table I need to check whether if the the same customer is ordering again . if the customer matching then I need to move first the existing data from the custtotnew table to custtotold table, then refresh the custtotnew table with the latest file

custtotalold

custtotnew

Customerid ¦ custname ¦ product¦year¦month¦quantity

1¦ abc¦ cpppp¦2015¦01¦100

2¦ bec¦ pqyert¦2015¦02¦200

3¦ lmp¦ lmkkjs¦2015¦03¦000

4¦ raj¦ kkkjsdd¦2015¦04¦10

5¦ ras¦ cppsds¦2015¦05¦14

Re: oracle move

marcus_malinow — Fri, 06 Nov 2015 12:17:19 GMT

Something like this might work

//first off load all existing customer id's

CustTotNewToMove:

LOAD

Customerid as Customeridtomove

FROM

custtotnew.qvd (qvd)

;

//then work out which we have new records for

INNER JOIN (CustTotNewToMove)

LOAD

Customerid as Customeridtomove

FROM

[your new data file]

;

//then load existing custtotold records

CustTotOld:

LOAD

FROM custtotold.qvd (qvd)

;

//and append those from custtotnew where we have new records

CONCATENATE (CustTotOld)

LOAD

FROM custtotnew.qvd (qvd)

WHERE EXISTS (Customeridtomove, Customerid)

;

//and store, and drop

STORE CustTotOld;

DROP TABLE CustTotOld;

//then load new data

CustTotNew:

LOAD

FROM

[your new data file]

;

//and append custtotnew records where we don't have new data

CONCATENATE (CustTotNew)

LOAD

FROM custtotnew.qvd (qvd)

WHERE NOT Exists (Customerid);

Re: oracle move

raadwiptec — Fri, 06 Nov 2015 13:03:04 GMT

basically I need to move the lines not copy.. any other suggestions

Re: oracle move

marcus_malinow — Fri, 06 Nov 2015 13:06:39 GMT

Ok and that's what my code above would do.