topic Merging data in a resident table in QlikView

Merging data in a resident table

Wed, 20 Aug 2014 13:56:20 GMT

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

Re: Merging data in a resident table

Wed, 20 Aug 2014 15:19:34 GMT

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

Qlikview Incremental Load

Re: Merging data in a resident table

Wed, 20 Aug 2014 15:27:57 GMT

Guy,

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

-Ram

Re: Merging data in a resident table

Thu, 21 Aug 2014 06:01:44 GMT

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

Re: Merging data in a resident table

Thu, 21 Aug 2014 09:18:06 GMT

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