Adding records to an existing table

[#1]

Hello there, me again, hope someonce can advise.

I want to run a daily process that brings in records in an 'Outstanding' status, and records their 'Record ID', the 'Current Date' and the 'Team' that they are assigned to.

I want to add a new set of records that meet the 'Outstanding' criteria each day, so that i can trend the volume of 'Outstanding' Records. What i dont want to happen is for the table to be completelty refreshed each day, i want all previous records to remain, and a new block or 'Outstanding' Records to be added to the bottom of the table.

e.g.

Day 1 run creates

Outstanding Records:

ID, Date. Team

1, Day1, TeamA

3, Day1, TeamA

5, Day1, TeamB

Day 2 Run adds new records to the table, tagged as Day2

ID, Date, Team

1, Day1, TeamA

3, Day1, TeamA

5, Day1, TeamB

3, Day2, TeamA

5, Day2, TeamB

8, Day2, TeamA

I would also like to only store each record ID once for each day, so if i run the import again on the same day i want to avoid creating duplicate entries in the table.

Can someone advise how to add the new records to an existing table without creating duplicate records?

Thanks...

[#2 · Accepted solution]

Hi,

If i understand well, you have one table by day ?

so , you can do with ADD Load and Partial Reload (CTRL+SHIFT+R).

But if you want to introduce a loop maybe the best way is to create a QVD file :

1) read QVD of your data

2) read new data for the current day,

3) set the data thanks to Add Load

4) Update your QVD

look at the reference manual to understand how to create a QVD file

Regards

JJ

[#3]

Thank you for this, i can now create a qvd file and add to it on a daily basis. The final thing i need is to add a field to the qvd file that includes the date that the record is added to the qvd file?

I've tried doing this by setting a variable of Today() and loading this variable as the extra field

OutstandingRecords:

Let

ThisExecTime = Today();

Load

Status,

PK_ID2

,

ThisExecTime;

SQL

SELECT

"FK_Status" AS Status,

"PK_ID"

FROM

"DataSource".dbo.FactCall

WHERE

"FK_Status" NOT LIKE

'CL';

But i get an error saying the field does not exist. 😞

[#4]

You need to identify it as a variable as

$(ThisExecTime) as DateField

Regards,

Gordon

[#5]

Ignore me, i've sorted it.

[#6]

Yes, thanks gordon, that's done it.