topic Adding records to an existing table in QlikView

Adding records to an existing table

Sun, 21 Feb 2010 12:04:15 GMT

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...

Adding records to an existing table

Sun, 21 Feb 2010 12:57:52 GMT

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

Adding records to an existing table

Mon, 22 Feb 2010 12:08:24 GMT

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. 😞

Adding records to an existing table

Mon, 22 Feb 2010 12:12:21 GMT

You need to identify it as a variable as

$(ThisExecTime) as DateField

Regards,

Gordon

Adding records to an existing table

Mon, 22 Feb 2010 12:28:22 GMT

Ignore me, i've sorted it.

Adding records to an existing table

Mon, 22 Feb 2010 12:29:22 GMT

Yes, thanks gordon, that's done it.