Solved: Re: How to fill empty cell under conditions - Qlik Community

Report Inappropriate Content · ‎2016-08-22

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 · ‎2016-08-22

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;

View solution in original post

bindu_apte · ‎2016-08-22

Hi,

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

Report Inappropriate Content · ‎2016-08-22

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 · ‎2016-08-22

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 · ‎2016-08-22

Might be this,

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

sunny_talwar · ‎2016-08-22

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

Uploading a Sample

Report Inappropriate Content · ‎2016-08-22

See the attached excel file.

Hope that thsi can be solved using a script.

Report Inappropriate Content · ‎2016-08-22

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 · ‎2016-08-22

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;

Report Inappropriate Content · ‎2016-08-22

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