I'm trying to create an application that will help us understand how we are affected when currencies fluctuate. And the reason I'm writing is of course that I have run into some problems.
Basically I have PurchaseOrders and Invoices. My PurchaseOrder data knows the in-price , date (indelivery date), currency and the exchange rate we have bought the product for.
My Invoice data knows the date (we deliver it), the out-price our customer paid, and also currency and exchange rate.
The problem I have is how to connect the data so I get the correct in-price for each Invoice.
Example: <b>PurchaseOrder</b> Article - Date - Inprice - Currency - Exchange rate A - 1/1 - 10 - EUR - 9,2 A - 15/1 - 11 - EUR - 9,0 A - 15/2 - 15 - USD - 6,5
<b>Invoice</b> Article - Date - Outprice - Currency - Exchange rate A - 5/1 - 179 - SEK - 1.0 A - 20/1 - 154 - NOK - 1.25 A - 10/2 - 179 - SEK - 1.0
How can I get QV to get the latest purchaseorder that exist that is before the Invoice. E.g. The first invoice above should be connected to the first PO. The second invoice should be connected to the seccond PO. But the 3rd invoice should be connected to the 2nd PO since the date is before PO 3 is delivered to us.
Below is the script as it is today but do not pay to much attention to it since I'm not sure it is the best way to solve it.
I have looked at the lookup() function and firstvalue() and firstsortedvalue() but not found a way that works.
In the script below PurchaseOrders are called Inköpsordrar and Invoices are called Försäljning.
Anyone that has an ideas on how to solve this?
Inkops_Valuta: mapping LOAD * INLINE [ LEVERANTÖRSLAND, INKÖPSVALUTA SE, SEK NO, NOK FI, EUR US, USD DE, EUR GB, GBP DK, DKK IT, EUR NL, EUR PL, PLN ];
//////////////////////////////////////////////Försäljning steg 1///////////////////// Försäljning: LOAD StockItem as ArtikelNr, ProductClass FROM D:\QlikviewSystems\PCD\Harmoney\2_Transform\HY_Article_StockItem_Head.qvd (qvd) where ProductClass = 70;
//Tot_Faktura_fok_date: left join (Försäljning) LOAD FOKID, FakturaNr, Faktura_Fakturadatum as Date, Faktura_Fakturadatum, ArtikelNr, // Antal, // Pris, // Kalkylpris, // Kurs, Sum(Kalkylpris * Antal) as Tot_Kalkylpris_FOK_Date, Sum(Pris * Antal) as Totalsumma_FOK_Date, Sum(Kurs * Pris * Antal) as Totalsumma_FOK_Date_i_SEK, applymap('FOK_Valuta', FOKID, 'Missing') as Försäljningsvaluta FROM D:\QVD\PCD\ExportQVD\2_Transform\Faktura.qvd (qvd) where exists(ArtikelNr) group by FOKID, Faktura_Fakturadatum, ArtikelNr, FakturaNr;
SortFakturor: Load FOKID, FakturaNr, Faktura_Fakturadatum, Faktura_Fakturadatum as Date, ArtikelNr, Tot_Kalkylpris_FOK_Date, Totalsumma_FOK_Date, Totalsumma_FOK_Date_i_SEK, Försäljningsvaluta Resident Försäljning where FakturaNr > 0 order by ArtikelNr, Faktura_Fakturadatum;
store SortFakturor into D:\QlikviewSystems\PCD\Harmoney\3_Load\Invoices_ordered_by_date.qvd (qvd); //drop table SortFakturor; drop table Försäljning;
///////////////////////////////////////Inköpsordrar/////////////////////// /* Inköpsordrar: LOAD StockItem as LAGARTNR, ProductClass // Name FROM D:\QlikviewSystems\PCD\Harmoney\2_Transform\HY_Article_StockItem_Head.qvd (qvd) where ProductClass = 70;
left join (Inköpsordrar) LOAD LEVID, LAGARTVARID, LAGARTNR FROM D:\QVD\PCD\ExportQVD\2_Transform\Valutaeffekt_koppling.qvd (qvd) where exists(LAGARTNR);
left join (Inköpsordrar) LOAD INKPR, INKORDID, LAGARTVARID, INKORDNR, // ANTBEST, ANTINLEVNS, INLEVERANSDATUM // INLEVERANSDATUM as Date FROM D:\QVD\PCD\ExportQVD\2_Transform\Valutaeffekt_inkpris.qvd (qvd) where exists(LAGARTVARID) AND ANTINLEVNS > 0 AND INLEVERANSDATUM > '2010-06-01';
//Valukurs: left join(Inköpsordrar) LOAD If(VALUKURS > 0,VALUKURS) as VALUKURS, If(VALUKURS > 0,INKORDID) as INKORDID FROM D:\QVD\PCD\ExportQVD\2_Transform\Valutaeffekt_valukurs.qvd (qvd);
//Levland: left join (Inköpsordrar) LOAD LEVERANTÖRSLAND, applymap('Inkops_Valuta',LEVERANTÖRSLAND,'Missing') as Inköps_Valuta, LEVID FROM D:\QVD\PCD\ExportQVD\2_Transform\Valutaeffekt_levland.qvd (qvd) where exists(LEVID);
drop field ProductClass;
SorteradeInköpsordrar: noconcatenate Load *, LAGARTNR as ArtikelNr Resident Inköpsordrar where VALUKURS > 0 order by LAGARTNR, INLEVERANSDATUM;
store SorteradeInköpsordrar into D:\QlikviewSystems\PCD\Harmoney\3_Load\SorteradeInköpsordrar.qvd (qvd); drop table SorteradeInköpsordrar; drop table Inköpsordrar; */
//Here is where where I need to find a way to get the "correct" values from PurchaseOrders and join that to each Invoice left join (SortFakturor) Load INKPR, ANTINLEVNS, INLEVERANSDATUM, VALUKURS, Inköps_Valuta, ArtikelNr FROM D:\QlikviewSystems\PCD\Harmoney\3_Load\SorteradeInköpsordrar.qvd (qvd);
