Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arnould_it
Contributor III
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

1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

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..

View solution in original post

9 Replies
sushil353
Master II
Master II

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
Contributor III
Contributor III
Author

Thanks

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

MK9885
Master II
Master II

Maybe the attached Sample?

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

Untitled.png

arnould_it
Contributor III
Contributor III
Author

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)

MK9885
Master II
Master II

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
Contributor III
Contributor III
Author

Like below:

EmptyDatesProblem_expected.png

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

kaanerisen
Creator III
Creator III

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..

MK9885
Master II
Master II

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
Contributor III
Contributor III
Author

Hi Kaan,

thanks, it works good.

BR

Alessandro