Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
>>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;
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.
Hi Please find the scenario explained above to give you more clarity
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.
This is still not working. I still have the new records from Cargo_New.qvd into Cargo.qvd.
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.
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.