Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi to everyone,
I need to load rows from an Excel file like below:
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
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:
Hope it helps..
There are many threads on the same topic : Fill in missing dates till today
or else upload excel data for the solution.
HTH
sushil
Thanks
meanwhile I investigate on your suggestions here is the source Excel file attached.
Maybe the attached Sample?
Please check data in Sheet 2 for XL....
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)
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
Like below:
I have not a QlikView license, only Qlik Sense, so I can't view your 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:
Hope it helps..
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.
Hi Kaan,
thanks, it works good.
BR
Alessandro