INSERT COLUMS PREVIOUS DATA

[inigoelv]

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

[#2]

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

[ngulliver]

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