Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with the product cost(A) and another table with the cost from invoices(B).
I would like to find from the A table the item cost and find the last invoice cost from B table
Anyone knows how to do this?
Many thank's
Eduard
You're welcome.
Think about what will happen if two prices repeat over the time !!!!!
Joaquín
!@if i understood what you need is to find for each item, the last invice of that item and get the cost from that invoice and then have the new cost show next to the cost from the items table
try this:
Temp_ItemCosts:
LOAD * INLINE [
Item, DateCost, ItemCost
AA, 15/05/2015, 2.00
AA, 20/04/2015, 2.15
AA, 01/03/2015, 1.75
];
Temp_InvoiceCosts:
LOAD * INLINE [
Invoice, Item, Date, CostInvoice
5555, AA, 30/04/2015, 2.00
4444, AA, 28/04/2015, 3.00
3333, AA, 15/04/2015, 2.15
2222, AA, 05/02/2015, 1.80
1111, AA, 31/12/2014, 1.75
];
Temp_InvoiceCosts1:
LOAD
Invoice,
Item,
Date as FromDate,
CostInvoice,
IF(IsNull(PEEK('Item')) or PEEK('Item') <> Item,Date(Today()),Date(peek('FromDate')-1)) as ToDate
Resident Temp_InvoiceCosts
Order By Item,Date desc;
DROP Table Temp_InvoiceCosts;
left join
IntervalMatch (DateCost,Item) load FromDate, ToDate,Item resident Temp_InvoiceCosts1;
Right Join
LOAD
*
Resident Temp_ItemCosts;
DROP Table Temp_ItemCosts;
I think the 3rd arrow should go to 1111.
Anyway. What's that?
you can't relate data by cost. what if 2 items have the same cost?
seeing is believing
i think what he wants was t ofind the last invoce and take the cost from there
not to link the table by cost
Hi Daniel,
Yes that's what I'm looking for, and I tryied to follow your code and there is a problem and sorry, I could not find it:
""Field not found
left join
IntervalMatch (INVOICEDATE,ITEMID) load FromDate, ToDate,ITEMID resident VENDINVOICETRANS1"
//Temp_ItemCosts:
//
//LOAD * INLINE [
//Item, DateCost, ItemCost
//AA, 15/05/2015, 2.00
//AA, 20/04/2015, 2.15
//AA, 01/03/2015, 1.75
//];
INVENTTRANS:
LOAD * from $(VStore)INVENTTRANS.qvd (qvd);
VENDINVOICETRANS:
LOAD ITEMID AS ITEMID_WAC,
Trim(ITEMID)&'+'&unit_price as KeyRC,
*;
SQL Select * From AX_SQL_VENDINVOICETRANS;
//Temp_InvoiceCosts:
//LOAD * INLINE [
//Invoice, Item, Date, CostInvoice
//5555, AA, 30/04/2015, 2.00
//4444, AA, 28/04/2015, 3.00
//3333, AA, 15/04/2015, 2.15
//2222, AA, 05/02/2015, 1.80
//1111, AA, 31/12/2014, 1.75
//];
VENDINVOICETRANS1:
LOAD
INVOICEID,
ITEMID,
INVOICEDATE as FromDate,
unit_price,
IF(IsNull(PEEK('ITEMID')) or PEEK('ITEMID') <> ITEMID,Date(Today()),Date(peek('FromDate')-1)) as ToDate
Resident VENDINVOICETRANS
Order By ITEMID,INVOICEDATE desc;
DROP Table VENDINVOICETRANS;
left join
IntervalMatch (INVOICEDATE,ITEMID) load FromDate, ToDate,ITEMID resident VENDINVOICETRANS1;
Right Join
LOAD
*
Resident INVENTTRANS;
DROP Table Temp_ItemCosts;.
Many many thank's
i dont know what you have in your qvd table
the fields "INVOICEDATE" and "ITEMID" must be in the table --> INVENTTRANS.qvd
HI Eduard:
Here you have the document with the dates related by an interval match.
Regards
Joaquín
Hi Joaquin
Still with problems, I applied your script to my data model and i could not match the info, there are 2 syn and the data is not linked, could you take a view?
Many thank's and sorry for the terrible headache
Regards
Hi Eduard:
The syntetic key is correct and you should keep it, read this IntervalMatch
I'm having a look to your app.
Regards
Joaquín
Hi:
Temp_RealCosts:
LOAD * from $(VStore)INVENTTRANS.qvd (qvd);
// This line in the previous LOAD
DATE(DATEFINANCIAL_LINE, 'YYYY/MM/DD') as DATEFINANCIAL_LINE,
RealCosts:
LOAD ITEMID,
IF (Not IsNUll(Previous(Cost_WAC)), Date(Previous(DATEFINANCIAL_LINE) + 1, 'YYYY/MM/DD'), Date('2011/01/01', 'YYYY/MM/DD')) as DateCostFrom, DATE(DateCostFrom, 'YYYY/MM/DD') as DateCostFrom,
Cost_WAC as RealCost
Resident Temp_RealCosts
Order by DATEFINANCIAL_LINE;
Drop Table Temp_RealCosts;
InvoiceCosts:
LOAD INVOICEID AS Invoice,
ITEMID as Item,
DATE(INVOICEDATE, 'YYYY/MM/DD') as Date,
unit_price as Cost_Invoice;
SQL Select * From AX_SQL_VENDINVOICETRANS;
IntervalMatch:
IntervalMatch (Date)
Load distinct DateCostFrom, DATEFINANCIAL_LINE resident RealCosts;
I guess your dates don't match beacuse they're in different formats
See you soon
Joaquín