Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;