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

[bindu_apte]

Hi,

Can you explain in detail? If you want to fill empty boxes with "m" then you can use like this below snapshot.

[#3]

Hello Bindu,

The question is whetehr you can solve this by using a script, not via table properties.

So, script programming is the question.

Does this make sense?

Best regards,

Cornelis

[bindu_apte]

Hi,

Hope this thread will help you.

How to replace Null() by a string value?

Remove Null values in the script itself | Qlik Community

[Anil_Babu_Samineni]

Might be this,

If(Sum(Sales),Sales,'M')

[sunny_talwar]

Would you be able to provide the above data in an excel file?

Uploading a Sample

[#7]

See the attached excel file.

Hope that thsi can be solved using a script.

[#8]

Hello Sunny,

Thank you for your mail.

I have attached the excel file and hope that you can find a solution.

Best regards,

Cornelis

[sunny_talwar]

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;

FinalTable:

LOAD No,

  Score,

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

  If(Flag3 <= 4, Peek('Output'))) as Output

Resident Table;

[#10]

Hi Sunny,

Thank you for your kind contribution, that looks encouraging.

The FinalTable is almost complete, however, the last 3 records (No 22,23 and 24) must be '-' (Null values) in column output. Do you see a simple solution for this?

Best regards,

Cornelis