topic Re: How to fill empty cell under conditions in QlikView

How to fill empty cell under conditions

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to fill empty cell under conditions

bindu_apte — Mon, 22 Aug 2016 07:01:59 GMT

Hi,

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

Re: How to fill empty cell under conditions

Mon, 22 Aug 2016 07:06:10 GMT

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

Re: How to fill empty cell under conditions

bindu_apte — Mon, 22 Aug 2016 07:15:36 GMT

Hi,

Hope this thread will help you.

How to replace Null() by a string value?

Remove Null values in the script itself | Qlik Community

Re: How to fill empty cell under conditions

Anil_Babu_Samineni — Mon, 22 Aug 2016 08:06:00 GMT

Might be this,

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

Re: How to fill empty cell under conditions

sunny_talwar — Mon, 22 Aug 2016 10:14:39 GMT

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

Uploading a Sample

Re: How to fill empty cell under conditions

Mon, 22 Aug 2016 10:20:46 GMT

See the attached excel file.

Hope that thsi can be solved using a script.

Re: How to fill empty cell under conditions

Mon, 22 Aug 2016 10:22:22 GMT

Hello Sunny,

Thank you for your mail.

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

Best regards,

Cornelis

Re: How to fill empty cell under conditions

sunny_talwar — Mon, 22 Aug 2016 10:41:50 GMT

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;

Re: How to fill empty cell under conditions

Mon, 22 Aug 2016 10:59:52 GMT

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

Re: How to fill empty cell under conditions

sunny_talwar — Mon, 22 Aug 2016 11:08:07 GMT

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;

Re: How to fill empty cell under conditions

Mon, 22 Aug 2016 11:23:15 GMT

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