Need help with fetching previous data

[swati_rastogi27]

I have sample data as :

DATE_COL     EMP_ID     EMP_GRP_CODE     SOME_VALUE

01-JAN-16       11211          ABC                         23

01-JAN-16       11211         CDE                         34

01-JAN-16       11211          DEF                         45

02-JAN-16       11211          ABC                        

02-JAN-16       11211          CDE                        

02-JAN-16       11211          DEF                        

03-JAN-16       11211          ABC                        

03-JAN-16       11211          CDE                        

03-JAN-16       11211          DEF                        

04-JAN-16       11211          ABC                         67

04-JAN-16       11211          CDE                         89

04-JAN-16       11211          DEF                         90

05-JAN-16       11211          ABC                         11

05-JAN-16       11211          CDE                         12

05-JAN-16       11211          DEF                         13

My requirement is to roll/populate missing values for a combination of DATE_COL,EMP_ID and EMP_GRP_CODE.

Peek is not helping me, coz for row number 4, Peek will give me value from previous row, whereas , want value from row 1

Can someone help?

[Anonymous]

so what value do you want for row 4? Is it 23 as in row 1?

[swati_rastogi27]

Yes exactly . It should be 23

[antoniotiman]

Is this what You want ?

[swati_rastogi27]

Yes, this is exactly the output that I want.

Also , a simple group by on EMP_GRP_CODE will not work, because we have many EMP_IDs we well.

A peek without considering EMP_ID will result in fetching value from some other EMP_ID

Could you help me achieve this?

[antoniotiman · Accepted solution]

See attachment.

Regards,

Antonio

[Frank_Hartmann]

try this in script:

Test:

LOAD * INLINE [

DATE_COL,        EMP_ID,        EMP_GRP_CODE,    SOME_VALUE

01-JAN-16,       11211,         ABC,            23

01-JAN-16,       11211,         CDE,            34

01-JAN-16,      11211,          DEF,             45

02-JAN-16,      11211,          ABC,           

02-JAN-16,      11211,          CDE,             

02-JAN-16,      11211,          DEF,                     

03-JAN-16,      11211,          ABC,                     

03-JAN-16,      11211,          CDE,                     

03-JAN-16,      11211,          DEF,                     

04-JAN-16,      11211,          ABC,          67

04-JAN-16,      11211,          CDE,          89

04-JAN-16,      11211,          DEF,           90

05-JAN-16,      11211,          ABC,          11

05-JAN-16,      11211,          CDE,          12

05-JAN-16,      11211,          DEF,           13

];

1:

Load

DATE_COL,      

EMP_ID,      

EMP_GRP_CODE,

if(SOME_VALUE='',peek('SOME_VALUE1',-1),SOME_VALUE) as SOME_VALUE1

Resident Test order by EMP_GRP_CODE,DATE_COL;

DROP Table Test;

hope this helps

[sunny_talwar]

Frank I would change this to something like this:

LOAD DATE_COL,     

          EMP_ID,     

          EMP_GRP_CODE,

          If(EMP_ID = Previous(EMP_ID) and EMP_GRP_CODE = Previous(EMP_GRP_CODE) and SOME_VALUE='', Peek('SOME_VALUE1'), SOME_VALUE) as SOME_VALUE1

Resident Test

Order By EMP_ID, EMP_GRP_CODE, DATE_COL;

Because just checking if SOME_VALUE is null may not be enough because if for some reason the previous EMP_GRP_CODE is not the same, it might pull the value from another EMP_GRP_CODE. I guess it works in this scenario, but I think putting it there in code is a practice I always follow.

Best,

Sunny