Use Autohashnumber to load differential data

[Anonymous]

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

[Sergey_Shuklin]

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.

[jonathandienst]

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

[Anonymous]

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.

[Anonymous]

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

[Sergey_Shuklin]

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.

[Anonymous]

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

[Peter_Cammaert]

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.

[Peter_Cammaert]

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.