Create Data based on Start and End Date

[luca7]

Hi,

I am currently trying to explode my table so that I get a value for every date.

Example:
LOAD * INLINE [Product,Customer,Price,Cost,Date
4444,123,1.58025,1.55895,2022-10-10
4444,123,1.67019,1.64889,2022-10-19
4444,123,1.61818,1.59688,2022-10-27
4444,123,1.53070,1.50940,2022-11-07
4444,123,1.44931,1.41831,2022-11-11
4444,123,1.38543,1.35443,2022-11-23
4444,123,1.34208,1.32078,2022-11-30
4444,123,1.26694,1.23594,2022-12-09
4444,123,1.29378,1.27248,2022-12-15
4444,123,1.30395,1.28265,2022-12-28
5555,243,1.58025,1.55895,2022-10-10
5555,243,1.67019,1.64889,2022-10-19
5555,243,1.61818,1.59688,2022-10-27
5555,243,1.53070,1.50940,2022-11-07
5555,243,1.44931,1.41831,2022-11-11
5555,243,1.38543,1.35443,2022-11-23
5555,243,1.34208,1.32078,2022-11-30
5555,243,1.26694,1.23594,2022-12-09
5555,243,1.29378,1.27248,2022-12-15
5555,243,1.30395,1.28265,2022-12-28
]
;

My idea was to first create a StartDate and EndDate per Row so for example if we would look at:

Product,Customer,Price,Cost,Datetime

4444,123,1.61818,1.59688,2022-10-27
4444,123,1.53070,1.50940,2022-11-07

I would want to create a field ValidFrom 2022/10/27 and for this one the ValidTo would be the 2022/11/07 -1 day so 2022/11/06.

Product,Customer,Price,Cost,Datetime,ValidFrom,Validto

4444,123,1.61818,1.59688,2022-10-27,2022-10-27,2022-11-06
4444,123,1.53070,1.50940,2022-11-07,2022-11-07,2022-11-10

In SQL I can achieve this with the Lead function, how do I achieve this in the QV Script?

After this I would use want to explode this table with all possible dates for the year 2022 for example. Then I thought using IntervalMatch and ValidFrom + ValidTo dates I can match the correct Price and Cost to those dates. For example for 2022/10/31 I also want to have Price = 1.6181 and Cost = 1.59688.

So in the end I have a table with all Price and Cost per Product for every date.

Any help is greatly appreciated! Thanks!

[MarcoWedel]

https://community.qlik.com/t5/Design/Creating-Reference-Dates-for-Intervals/ba-p/1463944

[MarcoWedel]

one example

Example:
LOAD * INLINE [Product,Customer,Price,Cost,Date
4444,123,1.58025,1.55895,2022-10-10
4444,123,1.67019,1.64889,2022-10-19
4444,123,1.61818,1.59688,2022-10-27
4444,123,1.53070,1.50940,2022-11-07
4444,123,1.44931,1.41831,2022-11-11
4444,123,1.38543,1.35443,2022-11-23
4444,123,1.34208,1.32078,2022-11-30
4444,123,1.26694,1.23594,2022-12-09
4444,123,1.29378,1.27248,2022-12-15
4444,123,1.30395,1.28265,2022-12-28
5555,243,2.58025,2.55895,2022-10-10
5555,243,2.67019,2.64889,2022-10-19
5555,243,2.61818,2.59688,2022-10-27
5555,243,2.53070,2.50940,2022-11-07
5555,243,2.44931,2.41831,2022-11-11
5555,243,2.38543,2.35443,2022-11-23
5555,243,2.34208,2.32078,2022-11-30
5555,243,2.26694,2.23594,2022-12-09
5555,243,2.29378,2.27248,2022-12-15
5555,243,2.30395,2.28265,2022-12-28
]
;

Temp:
LOAD *,
     Date as ValidFrom,
     Date(If(Product=Previous(Product) and Customer=Previous(Customer),Previous(Date)-1,DayStart(YearEnd(Date))),'YYYY-MM-DD') as ValidTo
Resident Example
Order By Product,Customer,Date desc;

NoConcatenate
Result:
LOAD Product,
     Customer,
     Price,
     Cost,
     ValidFrom,
     ValidTo,
     Date(ValidFrom+IterNo()-1,'YYYY-MM-DD') as Date
Resident Temp
While ValidFrom+IterNo()-1 <= ValidTo
Order By Product,Customer,Date;

DROP Tables Example,Temp;