topic Re: Appending data without duplicates in QlikView

Appending data without duplicates

Ashti — Wed, 30 Nov 2022 20:05:53 GMT

I have a table saved down in qvd format and I concatenate that data with a table loaded from excel sheet

Issue:

Where not exists logic is not working.

Without that everytime you run the code; the no of records gets doubled up.

The second table attributes has table name appended to it where as first table do not

How do I concatenate two tables and if the data for that data already exists then we do not concatenate for that particular date

Code:

Table2:

LOAD

[ID],

[Geo Area] as [Table1.Geo Area],

[Status] as [Table1.Status],

[Date] as [Table1.Date]

FROM ['$(folder_path)'/*.xlsx]

(ooxml, embedded labels, table is Sheet1])

;

Concatenate Table2:

LOAD

[ID],

[Table1.Geo Area],

[Tabble1.Status],

[Tabble1.Date]

from

'$(folder_path)'/Table1.qvd(qvd)

where (not Exists([Table1.Date]));

Re: Appending data without duplicates

Vegar — Wed, 30 Nov 2022 22:46:10 GMT

You have different field names in the two tables [Table1.Date] and [Tabble1.Date]. You need to use the two parameter version of the exists() function.

Try this:

Code:
Table2:
LOAD
[ID],
[Geo Area] as [Table1.Geo Area],
[Status] as [Table1.Status],
[Date] as [Table1.Date]
FROM ['$(folder_path)'/*.xlsx]
(ooxml, embedded labels, table is Sheet1])
;

Concatenate Table2:
LOAD
[ID],
[Table1.Geo Area],
[Tabble1.Status],
[Tabble1.Date]
from
'$(folder_path)'/Table1.qvd(qvd)
where
not Exists([Table1.Date],[Tabble1.Date]);

Re: Appending data without duplicates

marcus_sommer — Thu, 01 Dec 2022 09:16:04 GMT

Assuming that your different fieldnames is just a typo the used incremental approach is structural and syntactically ok. But the values of the identifier- respectively the exists-fields must be logically suitable - which isn't the case if the dates of the current-load and the historical data could be overlapping. If at least one source has a date-completed dataset you could use date as exists-field whereby depending on the data you may need to switch the load-order and loading at first the historical ones and adding then the current ones.

Better as the dates would be if you could use an unique record-identifier. This is usually a field like your ID and if the ID alone isn't unique you may add some further fields to make it unique, for example by a field like an OrderID you may add the row-position (if not already such OrderLineID exists).

- Marcus