Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi QV
I have a requirement where I need to multiply the standard cost * exchrate(exch value should be based on max date),how should we achieve it in script level.
Output looks like
Currency | StandardCost*excg(excg value should be based on max date) |
---|---|
CHF | 0.25*1.2345 |
EUR | 2.00*2.52356 |
AFN | 0.56*0.2453 |
INPUT table1:
Currency | Standard cost |
---|---|
CHF | 0.25 |
AFN | 0.56 |
ALL | 1.56 |
EUR | 2.00 |
USD | 2.50 |
table2:
Currency | exch rate | Date | |
---|---|---|---|
CHF | 1.2345 | 10/08/14 | |
CHF | 1.1503 | 14/08/13 | |
CHF | 1.223 | 28/07/14 | |
EUR | 2.5123 | 15/07/14 | |
EUR | 2.52356 | 02/08/14 | |
AFN | 0.2345 | 12/02/14 | |
AFN | 0.2453 | 28/02/14 |
thanks
Hi Swathi
[Currency Standard Cost]:
LOAD Currency,
[Standard cost]
FROM
Task.xlsx
(ooxml, embedded labels, table is [Currency Standard Cost]);
[Exchange Rate]:
LOAD Currency,
[exch rate],
Date,
max(Date) as maxDate
FROM
Task.xlsx
(ooxml, embedded labels, table is [Exchange Rate])
group by Currency,
[exch rate],
Date;
[Max Date Exchange Rate]:
Load Currency,
[exch rate],
Date Resident [Exchange Rate] where Date=maxDate;
Drop Table [Exchange Rate];
Please find the solution in attachment
Thank you Ashraf,
I am using personal QV edition so can you please paste the script once.
Hi Swathi
[Currency Standard Cost]:
LOAD Currency,
[Standard cost]
FROM
Task.xlsx
(ooxml, embedded labels, table is [Currency Standard Cost]);
[Exchange Rate]:
LOAD Currency,
[exch rate],
Date,
max(Date) as maxDate
FROM
Task.xlsx
(ooxml, embedded labels, table is [Exchange Rate])
group by Currency,
[exch rate],
Date;
[Max Date Exchange Rate]:
Load Currency,
[exch rate],
Date Resident [Exchange Rate] where Date=maxDate;
Drop Table [Exchange Rate];
Thank you so much Ashraf....I will check the solution and mark it.
Hi Swathi is above solution resolved your problem or not. If your problem is solved then pleased marked it as Correct
Thanks