topic Re: Need help with fetching previous data in QlikView

Need help with fetching previous data

swati_rastogi27 — Thu, 04 Aug 2016 09:48:28 GMT

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?

Re: Need help with fetching previous data

Anonymous — Thu, 04 Aug 2016 09:56:08 GMT

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

Re: Need help with fetching previous data

swati_rastogi27 — Thu, 04 Aug 2016 10:08:12 GMT

Yes exactly . It should be 23

Re: Need help with fetching previous data

antoniotiman — Thu, 04 Aug 2016 10:16:24 GMT

Is this what You want ?

Re: Need help with fetching previous data

swati_rastogi27 — Thu, 04 Aug 2016 10:22:46 GMT

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?

Re: Need help with fetching previous data

antoniotiman — Thu, 04 Aug 2016 10:25:10 GMT

See attachment.

Regards,

Antonio

Re: Need help with fetching previous data

Frank_Hartmann — Thu, 04 Aug 2016 10:40:26 GMT

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

];

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

Re: Need help with fetching previous data

sunny_talwar — Thu, 04 Aug 2016 10:53:01 GMT

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