How to get previous available data

[#1]

Hello All,

I have data in table as below:

Field1, Field2, Year, Month, Value

f1a, f2a, 2013, 01, 10

f1b, f2b, 2013, 02, 20

f1c, f2c, 2013, 03, 30

f1d, f2d, 2013, 04, 40

f1e, f2e, 2013, 05, 50

f1f, f2f,  2013, 06, 60

f1g, f2g, 2013, 07, 70

f1h, f2h, 2013, 08, 80

f1h, f2h, 2013, 10, 90

Now here, in the above table i have data till aug (i.e month08). and no data for 9.

so same value of month8 should be forwarded to 9.

similarly, value of month 10 should be forwarded to 11, 12.

[its_anandrjs]

What will be for other fields like Year,Field1.Month and Field2 that will also peek the previous values. Then use the previous and peek functions.

[#3]

Hi Anand,

I have managed to fill the empty rows as below for the key of (Field1, Field2, Year)

Field1, Field2, Year, Month, Value

f1a, f2a, 2013, 01, 10

f1b, f2b, 2013, 02, 20

f1c, f2c, 2013, 03, 30

f1d, f2d, 2013, 04, 40

f1e, f2e, 2013, 05, 50

f1f, f2f,  2013, 06, 60

f1g, f2g, 2013, 07, 70

f1h, f2h, 2013, 08, 80

f1h, f2h, 2013, 09, 0

f1h, f2h, 2013, 10, 90

f1h, f2h, 2013, 11, 0

f1h, f2h, 2013, 12, 0

~Thanks & Regards

[tresesco]

Data:
Load IterNo() as Month AutoGenerate 1 While IterNo()<=12;
Left Join
Load * Inline [
Field1, Field2, Year, Month, Value

f1a, f2a, 2013, 01, 10

f1b, f2b, 2013, 02, 20

f1c, f2c, 2013, 03, 30

f1d, f2d, 2013, 04, 40

f1e, f2e, 2013, 05, 50

f1f, f2f,  2013, 06, 60

f1g, f2g, 2013, 07, 70

f1h, f2h, 2013, 08, 80

f1h, f2h, 2013, 10, 90
];
NoConcatenate
Final:

Load
If(IsNull(Field1), Peek(Field1), Field1) as Field1,
If(IsNull(Field2), Peek(Field2), Field2) as Field2,
If(IsNull(Year), Peek(Year), Year) as Year,
If(IsNull(Value), Peek(Value), Value) as Value,
Month;

Load
*
Resident Data Order By Month;

Drop Table Data;

[its_anandrjs]

Then load your table with use of peek functions

temp:

LOAD * Inline

[

Field1, Field2, Year, Month, Value

f1a, f2a, 2013, 01, 10

f1b, f2b, 2013, 02, 20

f1c, f2c, 2013, 03, 30

f1d, f2d, 2013, 04, 40

f1e, f2e, 2013, 05, 50

f1f, f2f,  2013, 06, 60

f1g, f2g, 2013, 07, 70

f1h, f2h, 2013, 08, 80

f1h, f2h, 2013, 10, 90

];

Join

LOAD * Inline

[

Month

01

02

03

04

05

06

07

08

09

10

11

12

];

NoConcatenate

Final:

LOAD

if(IsNull(Field1) = -1, Peek(Field1),Field1) as Field1,

if(IsNull(Field2) = -1, Peek(Field2),Field2) as Field2,

if(IsNull(Month) = -1, Peek(Month),Month) as Month,

if(IsNull(Year) = -1, Peek(Year),Year) as Year,

if(IsNull(Value) = -1,Peek(Value),Value) as Value Resident temp Order By Month Asc;

DROP Table temp;

[#6]

Hi tresesco,

Load * Inline [
  Field1, Field2, Year, Month, Value
  f1a, f2a, 2013, 01, 10
  f1a, f2a, 2013, 02, 20
  f1a, f2a, 2013, 03, 30
  f1a, f2a, 2013, 04, 40
  f1a, f2a, 2013, 05, 50
  f1a, f2a,  2013, 06, 60
  f1a, f2a, 2013, 07, 70
  f1a, f2a, 2013, 08, 80
  f1a, f2a, 2013, 10, 90
  f1b, f2b, 2013, 01, 10
  f1b, f2b, 2013, 02, 20
  f1b, f2b, 2013, 03, 30
  f1b, f2b, 2013, 04, 40
  f1b, f2b, 2013, 05, 50
  f1b, f2b,  2013, 06, 60
  f1b, f2b, 2013, 07, 70
  f1b, f2b, 2013, 08, 80
  f1b, f2b, 2013, 10, 90
  ];

can you try with this data

[MayilVahanan]

Hi

Try with this

Data:

Load * Inline [

  Field1, Field2, Year, Month, Value

  f1a, f2a, 2013, 1, 10

  f1a, f2a, 2013, 2, 20

  f1a, f2a, 2013, 3, 30

  f1a, f2a, 2013, 4, 40

  f1a, f2a, 2013, 5, 50

  f1a, f2a, 2013, 6, 60

  f1a, f2a, 2013, 7, 70

  f1a, f2a, 2013, 8, 80

  f1a, f2a, 2013, 10, 90

  f1b, f2b, 2013, 1, 10

  f1b, f2b, 2013, 2, 20

  f1b, f2b, 2013, 3, 30

  f1b, f2b, 2013, 4, 40

  f1b, f2b, 2013, 5, 50

  f1b, f2b, 2013, 6, 60

  f1b, f2b, 2013, 7, 70

  f1b, f2b, 2013, 8, 80

  f1b, f2b, 2013, 10, 90

  ];

Temp:

Load IterNo() as Month AutoGenerate 1 While IterNo()<=12;

Left Join

Load Field1 Resident Data;

Right Join(Data)

LOAD * Resident Temp;

NoConcatenate

Final:

Load

  If(IsNull(Field1), Peek(Field1), Field1) as Field1,

  If(IsNull(Field2), Peek(Field2), Field2) as Field2,

  If(IsNull(Year), Peek(Year), Year) as Year,

  If(IsNull(Value), Peek(Value), Value) as Value,

  Month;

Load

  *

Resident Data Order By Month;

Drop Table Data, Temp;

[#8]

No it is not giving correct output.

[MayilVahanan]

Hi

PFA

Can you say, what is the expected output?

[#10]

The highlighted data is wrong. it should f1a, f1a