How to fill empty cell under conditions

[#1]

Dear all,

See the table below.

The intention is to fill the gaps with 'm' as demonstrated in column Output.

The gap between 'm'  is not more than 4 cells.

As what you can see, column No with value 6 and 7 agrees with the requirement <= 4 cells, but No 12 - 19 not and therefore no fill up in coilumn Output.

Could you provide a script that can handle in this kind of situation?

Thanks,

Cornelis

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD No,

    Score

FROM

[example (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join (Table)

LOAD No,

  If(Len(Trim(Score)) = 0, RangeSum(Peek('Flag1'), 1), 0) as Flag1,

  If(Len(Trim(Score)) = 0, Alt(Peek('Flag2'), 1), RangeSum(Peek('Flag2'), 1)) as Flag2

Resident Table

Order By No;

Left Join (Table)

LOAD Flag2,

  Max(Flag1) as Flag3

Resident Table

Group By Flag2;

Left Join (Table)

LOAD No,

  If(Len(Trim(Score)) = 0, Alt(Peek('Flag4'), 1), RangeSum(Peek('Flag4'), 1)) as Flag4

Resident Table

Order By No desc;

FinalTable:

LOAD No,

  Score,

  If(Len(Trim(Score)) > 0, Score,

  If(Flag3 <= 4 and Flag4 > 1, Peek('Output'))) as Output

Resident Table;

DROP Table Table;

[#3]

Hi Sunny,

This is excellent, it is a good gap-filling script!

The construction of this script is quite different towards the one that I have used before.

I have tested your script also for 5 empty records and it works good, too.

Thank you for your kind support, I have again learned a lot.

Best regards,

Cornelis