Merging data in a resident table

[#1]

Hi all,

I  pull data from a cache database into a qvd file daily via extractor job.

I will create a resident table from this called InvFiscalCode, it has 3 columns InvNo, FiscalYM (format: YYYY-MM created from current date when loaded into this table), Code.

I need to update code if it changes for InvNo and FiscalYM everyday or insert row if InvNo,FiscalYM not found.

At the end of each month I will have the latest info for that row.

This process will start again for the next month.

We need to build a history so that we can track code changes by InvNo, FiscalYM going forward.

A general idea of the syntax would be really helpful.

I am quite new to Qlikview so my developing days are starting...

Thanks in advance.

Guy

[#2]

It looks what you need is an incremental load. Take a look at this:

Qlikview Incremental Load

[#3]

Guy,

Not very clear with your requirement. Can you please elaborate more?

-Ram

[#4]

Hi all and thanks for getting back to me.

here is a more detailed view of what I am trying to do:

This data is pulled from external database daily and stored in qvd file, they form a join by InvoiceNo

"INVOICETRANS2":

SELECT

rptinvnofiscal        as RptInvNoFiscal,

accountno        as AccountNo,

invoiceno        as InvoiceNo,

patientid        as PatientId,

ivid            as Ivid,

visitid            as VisitId,

invoicedate        as InvoiceDate,

invoicestatus        as InvoiceStatus,

invoicetype        as InvoiceType,

medicalaidfundcode    as MedicalAidFundCode,

medicalaidfunddesc    as MedicalAidFundDesc,

medicalaidnumber    as MedicalAidNumber,

employeetype        as EmployeeType,

hospitalcategory    as HospitalCategory,

hospitalsubcategory    as HospitalSubCategory,

totalrevenue        as TotalRevenue,

cancellations        as Cancellations,

ratechanges        as RateChanges,

netrevenue        as NetRevenue,

batchpayments        as BatchPayments,

cashierpayments        as CashierPayments,

delegations        as Delegations,

writeoffs        as WriteOffs,

systemtransactions    as SystemTransactions,

movement        as Movement

FROM x.accruals;

store "INVOICETRANS2" into $(vQVDDebtPath)INVOICETRANS2.qvd;

drop table "INVOICETRANS2";

"MACLAIMS":

select InvoiceNo, 

       nvl(AmountClaimed,0) as AmountClaimed,

       nvl(AmountPaid,0) as AmountPaid,

       nvl(AmountDueByMedicalAid,0) as AmountDueByMA,

       nvl(AmountDueByPatient,0) as AmountDueByPt,

       Status          as [MA Status Code]

from x.MedicalAidInvoices

where PatientID not in (select ExternalPatientNo from Z.TestPatients);

store "MACLAIMS" into $(vQVDDebtPath)MACLAIMS.qvd;

drop table "MACLAIMS";

I want to create a resident table InvFiscalCode based on the two tables above,

it has 3 columns InvoiceNo, FiscalYM (This column is populated with the date of when data is loaded into this resident table in format 'YYYY-MM'), MA Status Code.

example:

The current date is 2014/08/01

First load I will have row like:

1,2014-08,B1   (the current date is 2014/08/01)

Next daily load the current date is 2014/08/02

The row looks like this: 1,2014-08,H1

I now need to update the resident table InvFiscalCode so that it looks like this: 1,2014-08,H1

I now also have a new row like: 2,2014-08,Z1 which not in InvFiscalCode table and need to insert it.

I now have 2 rows that look like this:

1,2014-08,H1

2,2014-08,Z1

This process runs through the month.

At the end of 20014/08 the data then stays as is.

For 2014-09 after daily extraction the status for invoice may change or stay the same again:

invoice 1 may look like this:

1,2014-09,H1 (This now needs to be inserted based on invoice and FiscalYM)

and so this process continues.

What we want to see in this table over time is the latest status code for that invoice per month.

1,2014-08,H1

1,2014-09,H1

2,2014-08,Z1

I can do this in oracle SQL but need help with qlikview syntax etc.

Regards

Guy

[#5]

Hi Ram

Thanks for getting back to me.

Here is a more detailed view of what I am trying to do:

This data is pulled from external database daily and stored in qvd file, they form a join by InvoiceNo

"INVOICETRANS2":

SELECT

rptinvnofiscal        as RptInvNoFiscal,

accountno        as AccountNo,

invoiceno        as InvoiceNo,

patientid        as PatientId,

ivid            as Ivid,

visitid            as VisitId,

invoicedate        as InvoiceDate,

invoicestatus        as InvoiceStatus,

invoicetype        as InvoiceType,

medicalaidfundcode    as MedicalAidFundCode,

medicalaidfunddesc    as MedicalAidFundDesc,

medicalaidnumber    as MedicalAidNumber,

employeetype        as EmployeeType,

hospitalcategory    as HospitalCategory,

hospitalsubcategory    as HospitalSubCategory,

totalrevenue        as TotalRevenue,

cancellations        as Cancellations,

ratechanges        as RateChanges,

netrevenue        as NetRevenue,

batchpayments        as BatchPayments,

cashierpayments        as CashierPayments,

delegations        as Delegations,

writeoffs        as WriteOffs,

systemtransactions    as SystemTransactions,

movement        as Movement

FROM x.accruals;

store "INVOICETRANS2" into $(vQVDDebtPath)INVOICETRANS2.qvd;

drop table "INVOICETRANS2";

"MACLAIMS":

select InvoiceNo, 

       nvl(AmountClaimed,0) as AmountClaimed,

       nvl(AmountPaid,0) as AmountPaid,

       nvl(AmountDueByMedicalAid,0) as AmountDueByMA,

       nvl(AmountDueByPatient,0) as AmountDueByPt,

       Status          as [MA Status Code]

from x.MedicalAidInvoices

where PatientID not in (select ExternalPatientNo from Z.TestPatients);

store "MACLAIMS" into $(vQVDDebtPath)MACLAIMS.qvd;

drop table "MACLAIMS";

I want to create a resident table InvFiscalCode based on the two tables above,

it has 3 columns InvoiceNo, FiscalYM (This column is populated with the date of when data is loaded into this resident table in format 'YYYY-MM'), MA Status Code.

example:

The current date is 2014/08/01

First load I will have row like:

1,2014-08,B1   (the current date is 2014/08/01)

Next daily load the current date is 2014/08/02

The row looks like this: 1,2014-08,H1

I now need to update the resident table InvFiscalCode so that it looks like this: 1,2014-08,H1

I now also have a new row like: 2,2014-08,Z1 which not in InvFiscalCode table and need to insert it.

I now have 2 rows that look like this:

1,2014-08,H1

2,2014-08,Z1

This process runs through the month.

At the end of 20014/08 the data then stays as is.

For 2014-09 after daily extraction the status for invoice may change or stay the same again:

invoice 1 may look like this:

1,2014-09,H1 (This now needs to be inserted based on invoice and FiscalYM)

and so this process continues.

What we want to see in this table over time is the latest status code for that invoice per month.

1,2014-08,H1

1,2014-09,H1

2,2014-08,Z1

I can do this in oracle SQL but need help with qlikview syntax etc.

Regards

Guy