Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All.
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.
Thank you in advance.
Regards,
Daniel
the new final script:
Status_temp:
load InvoiceNo,
Status
from qvd (qvd);
Status_Map:
mapping load
InvoiceNo as Number,
Status as Status
resident Status_temp
order by InvoiceNo, Status DESC;
drop table Status_temp;
Status:
load *
where Status = Overallstatus
;
load *,
applymap('Status_Map',InvoiceNo,null()) as Overallstatus
from your qvd (qvd)
;
would you be able to provide some sample data so that we can suggest the better approach
Hi,
Please inform what you required.
i hope i can explain the need further below:
I have the following fields included:
[Invoice no.]
[Status]
where i am loading multiple files at once, the [Invoice no.] is repeated and in the final document giving the result in the image.
In the load i need to allow, 'Not Yet Posted' if this is the only Value and if there is a 'Posted' Value in [Status] only load the 'Posted'
The 'Not Yet Posted' will always be in the data before the 'Posted'.
If you can assist in a load where clause or if statement in the script, that would be great.
Hope this is enough for you to understand
Hi Kush,
Did you have any suggestion on this post?
Is it best to write an If statement associated to [Status] to only load value Posted if both Posted and Not Yet Posted exist?
or
a where clause for Where status = 'Posted','Not Yet Posted' only load the posted Values.
thanks in advance if you get a chance to assist in this.
My suggestion:
Status_Map:
mapping load
InvoiceNo,
Status
from your source
order by InvoiceNo, Status DESC;
So you will get a list of all your invoiceno, the second field is the status, sorted descending. So if invoiceno has postet and not yet posted, posted is the first entry. Otherwise only not yet posted is available.
Then load your datas:
load *
where Status = Overallstatus
;
load *,
applymap('Status_Map',InvoiceNo,null()) as Overallstatus
from your source
;
Regards
Thank you for the suggestion Martin,
I have not used Order by before, please can you assist in where that would be placed in the load script?
I have the below but the syntax in incorrect
Status_Map:
mapping load
[Invoice no.],
Status
FROM
[*******************************\SalesLUK.QVD]
(qvd)
Order by [Invoice no.], Status DESC
;
Thanks again for your help.
That's right, you can't do a "order by" from qvd.
So first load from qvd then mapping load resident qvd.table order by.
Regards
HI,
Apologies, this is new ground to me here.
So,
I load like this:
Status_Map:
mapping load
[Invoice no.],
Status
resident Sales
Order by [Invoice no.], Status DESC
;
Sales:
LOAD
******
FROM
[*********************************************\SalesLUK.QVD]
(qvd)
WHERE
Status = Overallstatus and
not match (Status,'Cancelled')
Where do i add the below please? Thanks again
applymap('Status_Map',InvoiceNo,null()) as Overallstatus
you can do something like below
Data:
Load Invoice,
Invoice_Line,
Status
FROM YourQVD;
Final:
NoConcatenate
Load Invoice,
Invoice as Invoice1,
Status,
Invoice_Line
Resident Data
where Status='Posted';
Concatenate(Final)
Load *
Resident Data
where not Exists(Invoice1,Invoice);
Drop Table Data;
Drop Field Invoice1;
this:
Sales:
LOAD
******
FROM
[*********************************************\SalesLUK.QVD]
(qvd)
WHERE
Status = Overallstatus and
not match (Status,'Cancelled')
you have to change:
Sales:
LOAD
*
WHERE
Status = Overallstatus and
not match (Status,'Cancelled')
;
load *,
applymap(....) as Overallstatus
FROM
[*********************************************\SalesLUK.QVD]
(qvd)
;