I need to avoid a situation where i am loading duplicate data for a [Status] of Both 'Posted' and 'Not Yet Posted'
If possible i need an if line in the load to say:
Load [Invoice no.] if [Status] = 'Not Yet Posted' but if [Status] For [Invoice no.] is duplicate with [Status] of 'Posted' and 'Not Yet Posted' only load Posted.
Basically i can not have an invoice with both status' in the final load.
I will be applying this if load statement to a load from a qvd.
If anyone can assist in creating the If statement to avoid [Invoice no.] duplication through [Status] it would be very much appreciated.
I will try this but first can you try to explain how this will load 'Not Yet Posted' if this is the only status and Only 'Posted' if Both 'Not Yet Posted' and 'Posted' Status exists for the same [Invoice no.]
Second load will load the data where status is 'posted' .This will include the invoices where status is only posted. In short it will include invoices where status is both but only posted is loaded and invoices just have posted.
Third load is checking the invoices from second load and loading invoices from first load which are not part of the second load. Hence here we will get invoices with status not posted only. It will not load invoices where status is both because those are already included in second load with posted status.
Finally we are combining second and third load to get all the invoices as required.
in the mapping table there is every ID and in one line posted and in a second line not yet posted. In another line there is only one line, not yet posted.
An applymap statement tooks the first result for each ID and maps it to the line in the complete records. So for the line with two entries (posted and not posted you will get posted, in the second example not posted). Then in the final load only those lines are loaded where ovall post = actual post, so in example one only one line (posted) is loaded, in the secon example there is only one line which is loaded.
