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 split values between missed Date/Hours

Hello,

could anyone help me in this?

I'm gathering data from an Excel file in which there's just some values, by date and hour.

During load I've to fill all the rows between two date/hours, automatically creating ALL the inter-current date/hours; I've also to divide the next value by all the inter-current rows count.

The problem are:

  1. how to fill all rows creating all the inter-current date/hours
  2. how to split the last values dividing them by the number of Days/Hours Between two dates, using this solution

Example:

EmptyDatesValues.png

I've to fill all the gaps between - for example - "03/11/2017 10:00" and "30/11/2017 10:00" with all missed date and hours inter-current, and splitting the last values (15.320 for example) by all the gap-filled rows.

To solve the problem I've followed a previous suggestion from a community member:

// test data

S:

load

    Date,

    Floor(rand()*100) as Value1,

    Floor(rand()*100) as Value2

inline [

Date

18/01/2018 03:00:00

17/01/2018 03:00:00

16/01/2018 16:00:00

16/01/2018 08:00:00

15/01/2018 22:00:00

31/12/2017 23:00:00

];

// add DateTo

S1:

load

    RowNo() as Id, Value1, Value2,

    Date, Alt(Peek(Date), Date) as DateTo

Resident S

Order By Date desc;

DROP Table S;

// add Hours between Date....DateTo

F:

load

    *,

    Timestamp(Date + (IterNo()-1) / (24)) as DateTime

Resident S1

While (Date + (IterNo()-1) / (24)) <= (DateTo);

DROP Table S1;

Is it a good way to solve both my problems? It doesn't seem work completely good, maybe something has to be refined.

Thanks in advance

Alessandro

3 Replies
dwforest
Specialist II
Specialist II

The script copies the values to all the dates/hours between Date and DateTo. The method to generate the "missing" rows is correct. if you want to divide the value, you'd need to determine the number of hours between Date and DateTo and divide the values accordingly

Value1/(DateTo - Date / 24) hourlyValue

arnould_it
Contributor III
Contributor III
Author

Hi David,

thanks, actually it's my need.

Have you any suggestion to do this at the best inside my script?

dwforest
Specialist II
Specialist II

Value1/(DateTo - Date / 24) hourlyValue