Peek Function

[Anonymous]

     Hi!

I am using peek function to calculate a value and create a field in load scrip, but I know, for the first load that it won't have the field to get the previous value of. So, Is there a way of doing a workaround in this error and keep the script loading?

Best Regards,

[sunny_talwar]

I don't think you will encounter any errors here. Peek() for 1 row is null, so you won't need to worry about anything.

[Anonymous]

So maybe there is an error in my script, lol!

PERCENTAGES:

LOAD TYPE,

  YEAR_MKT as YEAR,

  REG,

  ENH,

  I,

  STD,

     [NEW ENROLL],

     Number

FROM  $(vLocale)\PERCENTAGES_V1.qvd  (qvd) Where len(TYPE)>0;

NoConcatenate

PERCENTAGES_TMP:

load *

Resident PERCENTAGES Order by TYPE, REG, YEAR;

drop Table PERCENTAGES;

//LOAD ABOVE LOAD

load *,

  IF(Floor(Number*([NEW ENROLL]/100)) = 0 AND TYPE=Previous(TYPE) AND REG=Previous(REG),

     Floor(Previous(TOTAL_ELI_ACC) +  (Number*([NEW ENROLL]/100) - Floor(Number*([NEW ENROLL]/100)))),

     Floor(Number*([NEW ENROLL]/100)))      as TOTAL_ELIGIBLE_TYPE;

NEW_CALC_TMP2:

load

     Number,

     TYPE,

     YEAR,

     REG,

     ENH,

     I,

     STD,

     [NEW ENROLL],

  IF(TYPE=Previous(TYPE) AND REG=Previous(REG),

  IF(Floor(Number*([NEW ENROLL]/100)) > 0, 0,

      Previous(TOTAL_ELI_ACC) + (Number*([NEW ENROLL]/100) - Floor(Number*([NEW ENROLL]/100)))),

     IF(Floor(Number*([NEW ENROLL]/100)) > 0, 0,

     (Number*([NEW ENROLL]/100) - Floor(Number*([NEW ENROLL]/100)))))        as TOTAL_ELI_ACC

Resident PERCENTAGES_TMP Order by TYPE, REG, YEAR;

drop table PERCENTAGES_TMP;

[sunny_talwar]

What error are you getting? Can you share information regarding that?

[Anonymous]

for this line,

  Previous(TOTAL_ELI_ACC) + (Number*([NEW ENROLL]/100) - Floor(Number*([NEW ENROLL]/100)))),

using PREVIOUS().  It does not find the TOTAL_ELI_ACC field.

[sunny_talwar]

Where is this even loading from? Did you miss a resident load here?

[Anonymous]

This is a load in load!

[vishsaggi]

As sunny mentioned you have to have a resident load or like below: Try this

      NEW_CALC_TMP2:

load *,

  IF(Floor(Number*([NEW ENROLL]/100)) = 0 AND TYPE=Previous(TYPE) AND REG=Previous(REG),

    Floor(Previous(TOTAL_ELI_ACC) +  (Number*([NEW ENROLL]/100) - Floor(Number*([NEW ENROLL]/100)))),

    Floor(Number*([NEW ENROLL]/100)))      as TOTAL_ELIGIBLE_TYPE;

load

    Number,

    TYPE,

    YEAR,

    REG,

    ENH,

    I,

    STD,

    [NEW ENROLL],

  IF(TYPE=Previous(TYPE) AND REG=Previous(REG),

  IF(Floor(Number*([NEW ENROLL]/100)) > 0, 0,

      Previous(TOTAL_ELI_ACC) + (Number*([NEW ENROLL]/100) - Floor(Number*([NEW ENROLL]/100)))),

    IF(Floor(Number*([NEW ENROLL]/100)) > 0, 0,

    (Number*([NEW ENROLL]/100) - Floor(Number*([NEW ENROLL]/100)))))        as TOTAL_ELI_ACC

Resident PERCENTAGES_TMP Order by TYPE, REG, YEAR;

drop table PERCENTAGES_TMP;

[Anonymous]

Tried that, but it still not working.

I will do a FOR loop instead and do the calculations!   It is not that easy and fast, but works!