topic Re: Merging two same QVD's with set of same records in QlikView

Merging two same QVD's with set of same records

Anonymous — Wed, 03 Feb 2016 09:34:39 GMT

Hi,

I am trying to merge two same QVD's with set of same of records. After reloading that script, I am able to see double the records in that QVD. But in table box chart when pulling all columns, it is displaying only 10 records.

My doubt is when concatenate same QVD with set of same records, will double that record count in QVD.

Re: Merging two same QVD's with set of same records

sunny_talwar — Wed, 03 Feb 2016 09:36:49 GMT

Table box only show the unique combination of selected fields. To see the actual data count check the count from Table Viewer (Ctrl + T)

Re: Merging two same QVD's with set of same records

PrashantSangle — Wed, 03 Feb 2016 09:37:26 GMT

Hi,

Yes,

for cross check take listbox with any field and check its frequency.

Regards,

Re: Merging two same QVD's with set of same records

Colin-Albert — Wed, 03 Feb 2016 09:37:41 GMT

A table box only shows the unique rows in the selected fields is does not show all rows, unless each row has a unique identifier.

Re: Merging two same QVD's with set of same records

Anonymous — Wed, 03 Feb 2016 09:43:01 GMT

Thanks for your Quick reply Sunny

Bu I want to see unique records, I mean no duplicate. Is there any way to resolve this duplicate records.

Re: Merging two same QVD's with set of same records

Chanty4u — Wed, 03 Feb 2016 09:43:52 GMT

use joins...left join

Re: Merging two same QVD's with set of same records

PrashantSangle — Wed, 03 Feb 2016 09:44:40 GMT

Hi,

If you want unique records then why are you concatenate same QVD with same set of records again??

Regards

Re: Merging two same QVD's with set of same records

sunny_talwar — Wed, 03 Feb 2016 09:44:47 GMT

May be you can do a resident load with DISTINCT in the script before saving the data to a qvd?

Re: Merging two same QVD's with set of same records

Anonymous — Wed, 03 Feb 2016 09:48:51 GMT

Thanks,

Can't we use inner or right join. why only left?

Re: Merging two same QVD's with set of same records

Anonymous — Wed, 03 Feb 2016 09:51:30 GMT

This is how I am doing.

test:

load * from events.QVD

concatenate

load*from events.QVD

store test into path\events.QVD

Re: Merging two same QVD's with set of same records

Colin-Albert — Wed, 03 Feb 2016 09:51:55 GMT

I find it better to avoid joins in QlikView as far as possible. QlikView is not a database!

Qlik Design Blog : Don't join - use Applymap in... | Qlik Community

Re: Merging two same QVD's with set of same records

sunny_talwar — Wed, 03 Feb 2016 09:54:21 GMT

May be do this:

test_Temp:

load * from events.QVD

concatenate

load*from events.QVD

test:

NoConcatenate

LOAD DISTINCT *

Resident test_Temp;

store test into path\events.QVD

DROP Tables test, test_Temp;

or you can also use Where Exists with a unique identifier in both tables:

test:

load * from events.QVD

concatenate

load*from events.QVD

Where not Exists(ID); -> Assuming ID is a unique identifier

store test into path\events.QVD

Re: Merging two same QVD's with set of same records

aapurva09 — Wed, 03 Feb 2016 09:55:24 GMT

Use DISTINCT keyword after you concatenate the QVDs. You can also join the QVDs depending on the situation.

Re: Merging two same QVD's with set of same records

PrashantSangle — Wed, 03 Feb 2016 09:55:32 GMT

Hi,

Try Not Exist() in where clause while Concatenating 2nd QVD in where clause.

It will help you to avoid duplicate records while loading.

Reards

Re: Merging two same QVD's with set of same records

Colin-Albert — Wed, 03 Feb 2016 09:58:11 GMT

test:

load * from events.QVD

concatenate

load * from events.QVD

You are loading the same data twice so you are duplicating every row.

Why are you concatenating the data from events.qvd to the data already loaded?

If you want the distinct data then add the distinct command to create a distinct table

test:

load DISTINCT * from events.QVD

Re: Merging two same QVD's with set of same records

Anonymous — Wed, 03 Feb 2016 10:53:22 GMT

Thanks Sunny.

In second solution youhave mentioned where not exists(ID).

If we don't have any ID columns?

I have 10 key columns.

Re: Merging two same QVD's with set of same records

sunny_talwar — Wed, 03 Feb 2016 10:56:41 GMT

You can create a ID field by Concatenating all your 10 fields in that case:

test:

LOAD *,

AutoNumber(Field1&'|'&Field2&'|'.....) as ID

from events.QVD

concatenate

load*from events.QVD

Where not Exists(ID, AutoNumber(Field1&'|'&Field2&'|'.....));

store test into path\events.QVD

Re: Merging two same QVD's with set of same records

Anonymous — Wed, 03 Feb 2016 11:03:59 GMT

Thanks for ur wonderful help