Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

arnould_it
New Contributor III

How to fill empty recurring dates using script

Hi to everyone,

I need to load rows from an Excel file like below:

EmptyDatesProblem.png

I've to fill empty values (in the red squares) automatically with the first value (01/11/2017 for example) until it changes, and so on.

Which is the simplest way to do this using script?

Thanks in advance.

Alessandro

Tags (3)
1 Solution

Accepted Solutions
kaanerisen
Contributor III

Re: How to fill empty recurring dates using script

Hi,

Script:

LOAD

    if(len(trim(giorno))=0, peek(giorno,RecNo()-(ora)), giorno) as Date,

    giorno,

    ora,

    TFumi,

    PFumi,

    Portata,

    "Massa fumi"

FROM [lib://Desktop/TabellaFlussi_NOVEMBRE-2017.xls]

(biff, embedded labels, table is [Tabella Flussi$]);

Data Model:

Untitled.png

Hope it helps..

9 Replies
sushil353
Honored Contributor II

Re: How to fill empty recurring dates using script

There are many threads on the same topic : Fill in missing dates till today

or else upload excel data for the solution.

HTH

sushil

arnould_it
New Contributor III

Re: How to fill empty recurring dates using script

Thanks

meanwhile I investigate on your suggestions here is the source Excel file attached.

Khan_Mohammed
Honored Contributor II

Re: How to fill empty recurring dates using script

Maybe the attached Sample?

Please check data in Sheet 2 for XL....

Untitled.png

arnould_it
New Contributor III

Re: How to fill empty recurring dates using script

Thanks,

have you a Qlik Sense version?

I'm looking too many rows for each New Date/ora and I don't understand why

Each New Date/ora have to be unique

Starting from the initial Xls file I've just to fill empty date (giorno) cells, repeating the same date (giorno) for each empty cells until encountering a new date (giorno)

Khan_Mohammed
Honored Contributor II

Re: How to fill empty recurring dates using script

Well you asked for fill in the dates...

Ex: 01/11/2017 for Ora 1 then Ora 2 has missing date, hence I populated same date which is 01/11/2017. The date then changes to 02/11/2017 and is being repeated again....

Isn't this what you want?

And the script can be used both in QlikView and Qlik Sense....

Just copy paste the script and it will work.

Or maybe you are looking to repeat dates?

Like 01/11/2017 till 24/11/2017? again repeat for Ora 2?

Please post a screenshot how you want the dates to be

arnould_it
New Contributor III

Re: How to fill empty recurring dates using script

Like below:

EmptyDatesProblem_expected.png

I have not a QlikView license, only Qlik Sense, so I can't view your script.

kaanerisen
Contributor III

Re: How to fill empty recurring dates using script

Hi,

Script:

LOAD

    if(len(trim(giorno))=0, peek(giorno,RecNo()-(ora)), giorno) as Date,

    giorno,

    ora,

    TFumi,

    PFumi,

    Portata,

    "Massa fumi"

FROM [lib://Desktop/TabellaFlussi_NOVEMBRE-2017.xls]

(biff, embedded labels, table is [Tabella Flussi$]);

Data Model:

Untitled.png

Hope it helps..

Khan_Mohammed
Honored Contributor II

Re: How to fill empty recurring dates using script

I guess what you seeking is the reply from Kaan erisen below, that should work for you.

I generated dates which are different to your requirement.

Thanks.

arnould_it
New Contributor III

Re: How to fill empty recurring dates using script

Hi Kaan,

thanks, it works good.

BR

Alessandro

Community Browser