topic Re: How to Match and Load in QlikView

How to Match and Load

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all

Sunny T

I have data with two fields with text . in file 1 : like below

'Party and Node with some rows . and i have new files with same columns and data is adding to existing file so

i want to match the data and i want to create new file with matching data and newly added data into new file.

Re: How to Match and Load

soniasweety — Wed, 21 Dec 2016 10:51:01 GMT

i have tried below but not working

Old:

LOAD Party,

Node,

AutoNumberHash128(Party,Node) as Key

FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate(Old)

New:

LOAD Party,

Node

FROM

(ooxml, embedded labels, table is Sheet2)

where not Exists(Key);

Re: How to Match and Load

jonathandienst — Wed, 21 Dec 2016 10:58:37 GMT

Like this:

LOAD Party,

Node,

AutoNumberHash128(Party,Node) as Key

FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate(Old)

New:

LOAD Party,

Node

FROM

(ooxml, embedded labels, table is Sheet2)

where Not Exists(Key, AutoNumberHash128(Party,Node));

Re: How to Match and Load

jonathandienst — Wed, 21 Dec 2016 11:00:07 GMT

If you need Key in the front end, you might want to do this:

LOAD Party,

Node,

AutoNumberHash128(Party,Node) as Key

FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate(Old)

New:

LOAD Party,

Node,

AutoNumberHash128(Party,Node) as Key

FROM

(ooxml, embedded labels, table is Sheet2)

where Not Exists(Key, AutoNumberHash128(Party,Node));

Re: How to Match and Load

qlik_jaijhn — Wed, 21 Dec 2016 11:14:17 GMT

Use the DISTINCT Keyword and load the tables, this will remove the duplicate records and concatenate into single table with unique records. Please see the script below:-

RoughData:

LOAD DISTINCT Party,

Node

FROM

rough.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD DISTINCT Party,

Node

FROM

rough.xlsx

(ooxml, embedded labels, table is Sheet2);

Re: How to Match and Load

soniasweety — Thu, 22 Dec 2016 05:20:06 GMT

Sorry for late replay and miscommunication

jontydkpi

qlik.jaijhn

I need a separate column for the non matching data ?

how can i achieve this?

Re: How to Match and Load

soniasweety — Thu, 22 Dec 2016 05:20:30 GMT

Sorry for late replay and miscommunication

I need a separate column for the non matching data ?

how can i achieve this?

Re: How to Match and Load

PrashantSangle — Thu, 22 Dec 2016 05:30:19 GMT

Hi,

What is your final expected output????

Regards,

Re: How to Match and Load

soniasweety — Thu, 22 Dec 2016 05:34:10 GMT

Hi,

I need output as Old data as one column and newdata in one column and one more column as which newly added in newdata records to be disply

Re: How to Match and Load

soniasweety — Thu, 22 Dec 2016 05:43:34 GMT

for example i have 5records in old file and 7 records in new file i need one more column which is not matching in both file ..... that 2records i want to extract as seperate column

Re: How to Match and Load

Thu, 22 Dec 2016 11:00:19 GMT

Have a look at these tutorials, they might help:

Partial Reload in QlikView - YouTube

Partial Reload in QlikView - Part II - YouTube

Re: How to Match and Load

soniasweety — Thu, 22 Dec 2016 12:52:36 GMT

thanks for reply but it will not work anymore. because both files should compare and give the result as newly added records.

Re: How to Match and Load

qlik_jaijhn — Fri, 23 Dec 2016 08:13:34 GMT

Hi,

I think this following script may fix your issues. There is a new field called "DataType" which can identify your fresh record and the duplicate record.. Please try this, hope it works..

Data:

LOAD RecNo() AS ID,

Party&Node AS Key,

Party,

Node

FROM

rough.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD RecNo() AS ID,

Party&Node AS Key,

Party,

Node

FROM

rough.xlsx

(ooxml, embedded labels, table is Sheet2);

LEFT JOIN (Data)

LOAD DISTINCT

ID,

if(Key=previous(Key),peek("Row Number")+1,1) as "Row Number"

RESIDENT Data

ORDER BY Key DESC;

FinalData:

Load *,

If("Row Number"=1,'FreshData','DuplicateData') AS DataType

Resident Data;

Drop Table Data;

Exit SCRIPT;

Re: How to Match and Load

Anonymous — Fri, 23 Dec 2016 11:12:11 GMT

Hi Song,

Try this:

Old:

LOAD

Trim(Party) as Party,

Trim(Node) as Node,

AutoNumberHash128(Trim(Party)&'-'&Trim(Node)) as Key,

'Old' as Type

FROM rough.xlsx (ooxml, embedded labels, table is Sheet1);

New:

LOAD

Trim(Party) as Party,

Trim(Node) as Node,

AutoNumberHash128(Trim(Party)&'-'&Trim(Node)) as Key,

'New' as Type

FROM rough.xlsx (ooxml, embedded labels, table is Sheet2)

Where Not(Exists('Key',AutoNumberHash128(Trim(Party)&'-'&Trim(Node))));

Output:

Regards!