Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello:
I need the help of somebody for making a script.
I have two tables:
Product | Production Date |
---|---|
A | 01/01/2013 01:45 |
B | 01/01/2013 01:49 |
C | 01/01/2013 01:56 |
Reel | Consume Date |
---|---|
1 | 01/01/2013 01:40 |
2 | 01/01/2013 01:43 |
3 | 01/01/2013 01:47 |
4 | 01/01/2013 01:53 |
I would like to insert the previous two reels consumed to each product in the script.
In other words:
Product | Production Date | Reel 1 | Reel 2 |
---|---|---|---|
A | 01/01/2013 01:45 | 1 | 2 |
B | 01/01/2013 01:49 | 2 | 3 |
C | 01/01/2013 01:56 | 3 | 4 |
How can I do?
I am trying with ApplyMap but without succesfull.
Thanks in advance
Hi,
You can use the FirstSortedValue in your script like this:
Production:
LOAD Product,
Production,
num(Production) as ProductionNum
FROM
ProdCons.xlsx
(ooxml, embedded labels, table is Production);
Join
LOAD Reel,
Consumption,
num(Consumption) as ConsumptionNum
FROM
ProdCons.xlsx
(ooxml, embedded labels, table is Consumption);
Final:
LOAD Product,
Production,
FirstSortedValue(Reel, -ConsumptionNum, 2) as Reel1,
FirstSortedValue(Reel, -ConsumptionNum) as Reel2
Resident Production
Where ConsumptionNum <= ProductionNum
Group By Product, Production;
DROP Table Production;
I've also attached the excel data for reference.
Hope this helps
Hi, Inigo.
Have a look at this blog by Steve Dark.
It gives you a idea of how to use ApplyMap.
http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/
Thanks,
Neil