topic Re: Use Autohashnumber to load differential data in QlikView

Use Autohashnumber to load differential data

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

I am trying to load distinct values from two qvds. Both the qvds have the same data structure. I am doing the following but not achieving the exact result.

SET PKCargoKey=autonumberhash128(bookingId,Year,Month,WeekNo,CargoType);

Cargo:

Load *,$(PKCargoKey) as Pk from Cargo.qvd (qvd);

Concatenate

Load *,$(PKCargoKey) as Pk from Cargo_New.qvd (qvd)

Where not exists(Pk, $(PKCargoKey));

Can someone pls tell me what am i doing wrong here?

Cargo.qvd is the main qvd to which i would like to merge distinct records from Cargo_New.qvd not existing in Cargo.qvd

Re: Use Autohashnumber to load differential data

Sergey_Shuklin — Fri, 02 Jun 2017 06:16:02 GMT

Hello, Ashmita!

What is exact result?

And maybe for achieving distinct values you'll have to try Load Distinct without a "*"? Just one table for a key Pk and one with facts.

P.S.: Your avatar looks familiar.

Re: Use Autohashnumber to load differential data

jonathandienst — Fri, 02 Jun 2017 06:31:03 GMT

>>not achieving the exact result.

That's a little vague, so I am not sure what problem we are solving for. If you were expecting duplicate new Pk values from Cargo_New.qvd, then drop the load of the value Pk in the second load statement:

SET PKCargoKey=autonumberhash128(bookingId,Year,Month,WeekNo,CargoType);

Cargo:

Load *, $(PKCargoKey) as Pk from Cargo.qvd (qvd);

Concatenate(Cargo)

Load * from Cargo_New.qvd (qvd)

Where not exists(Pk, $(PKCargoKey));

Drop Field Pk;

Re: Use Autohashnumber to load differential data

Anonymous — Fri, 02 Jun 2017 06:33:01 GMT

Dear Sergey,

Thanks for your time. Here is what is expected.

Cargo.Qvd has

bookingId	Year	Month	WeekNo	CargoType
1001	2017	1	1	A
1002	2017	1	1	B
1003	2017	1	1	C
1004	2017	1	1	D

Cargo_New.qvd has a new entry. Which needs to be Concatenated with Cargo.qvd. This is what i mean by distinct when we compare the two QVDs:

bookingId	Year	Month	WeekNo	CargoType
1001	2017	1	1	A
1002	2017	1	1	B
1003	2017	1	1	C
1004	2017	1	1	D
1005	2017	1	1	E

The output Should be the following:

bookingId	Year	Month	WeekNo	CargoType
1001	2017	1	1	A
1002	2017	1	1	B
1003	2017	1	1	C
1004	2017	1	1	D
1005	2017	1	1	E

But The concatenation isn't working properly using Hash number... The last record is not getting fetched in the final output.

Re: Use Autohashnumber to load differential data

Anonymous — Fri, 02 Jun 2017 07:14:58 GMT

Hi Please find the scenario explained above to give you more clarity

Re: Use Autohashnumber to load differential data

Sergey_Shuklin — Fri, 02 Jun 2017 07:19:14 GMT

Try to make it this way:

t1:

Load bookingId,Year,Month,WeekNo,CargoType from qvd1;

Load bookingId,Year,Month,WeekNo,CargoType from qvd2

Where not (Exists(bookingId) and Exists(Year) and Exists(Month) and Exists(WeekNo) and Exists(CargoType))

;

After that you can create a hash key using resident or to continue transforming data.

Re: Use Autohashnumber to load differential data

Anonymous — Fri, 02 Jun 2017 09:35:19 GMT

This is still not working. I still have the new records from Cargo_New.qvd into Cargo.qvd.

Re: Use Autohashnumber to load differential data

Peter_Cammaert — Fri, 02 Jun 2017 10:07:04 GMT

Funny, your code & data posted in this thread works for me. Are you sure that E record isn't already present in your base QVD?

See attachment.

Re: Use Autohashnumber to load differential data

Peter_Cammaert — Fri, 02 Jun 2017 10:23:46 GMT

Also make sure that the first parameter in your Exists() call has the correct spelling. Exists() will not complain about a non-existent field.

I dropped my first posted because I made the error myself (and as a result my test script didn't work either). The code in your OP looks ok, but your actual script may require a quick check.