topic INSERT COLUMS PREVIOUS DATA in QlikView

INSERT COLUMS PREVIOUS DATA

inigoelv — Fri, 13 Sep 2013 23:51:54 GMT

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

Re: INSERT COLUMS PREVIOUS DATA

Sat, 14 Sep 2013 00:37:29 GMT

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

Re: INSERT COLUMS PREVIOUS DATA

ngulliver — Mon, 16 Sep 2013 09:51:15 GMT

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