Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
inigoelv
Contributor

INSERT COLUMS PREVIOUS DATA

Hello:

I need the help of somebody for making a script.

I have two tables:

ProductProduction Date
A01/01/2013 01:45
B01/01/2013 01:49
C01/01/2013 01:56

ReelConsume Date
101/01/2013 01:40
201/01/2013 01:43
301/01/2013 01:47
401/01/2013 01:53

I would like to insert the previous two reels consumed to each product in the script.

In other words:

ProductProduction DateReel 1Reel 2
A01/01/2013 01:4512
B01/01/2013 01:4923
C01/01/2013 01:5634

How can I do?

I am trying with ApplyMap but without succesfull.

Thanks in advance

2 Replies
Not applicable

Re: INSERT COLUMS PREVIOUS DATA

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
Valued Contributor III

Re: INSERT COLUMS PREVIOUS DATA

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

Community Browser