topic Create Data based on Start and End Date in QlikView

Create Data based on Start and End Date

luca7 — Fri, 24 Mar 2023 15:00:33 GMT

Hi,

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

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!

Re: Create Data based on Start and End Date

MarcoWedel — Fri, 24 Mar 2023 17:27:50 GMT

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

Re: Create Data based on Start and End Date

MarcoWedel — Fri, 24 Mar 2023 18:04:13 GMT

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;