topic Re: Creation of Table Using a Date Field in a Table in App Development

Creation of Table Using a Date Field in a Table

hermanitor — Thu, 11 Mar 2021 09:29:37 GMT

Hi.
I have a table that looks like the following

Asset ID	Date Registered
A	05/02/2008
B	21/06/2016
C	04/10/2020

Need to loop through this table and for each asset ID and I would like to create a new table which uses the date and creates years from the date registered so I can then fill the data from another measures tables with the spend per asset per defined year...So I want a table like this

Asset ID	Date Registered	Year #	Start Year	End Year
A	05/02/2008	Year 1	05/02/2008	04/02/2009
A	05/02/2008	Year 2	05/02/2009	04/02/2010
A	05/02/2008	Year 3	05/02/2010	04/02/20011
A	05/02/2008	Year n	05/02/2021

Does this make sense what I require?
Any queries, please ask
Thanks

Re: Creation of Table Using a Date Field in a Table

MayilVahanan — Thu, 11 Mar 2021 09:59:41 GMT

HI @hermanitor

Try like below

LOAD *, Year(DateRegistered)+IterNo()-1 as Year, 'Year '& IterNo() as YearName, MakeDate(Year(DateRegistered)+IterNo()-1, Month(DateRegistered), Day(DateRegistered)) as StartYear
, MakeDate(Year(DateRegistered)+IterNo()-1+1, Month(DateRegistered), Day(DateRegistered)-1) as EndYear
INLINE [
Asset ID, DateRegistered
A, 05/02/2008
B, 21/06/2016
C, 04/10/2020
]while Year(DateRegistered)+IterNo()-1 <= Year(Today()) ;

O/p:

Re: Creation of Table Using a Date Field in a Table

hermanitor — Thu, 11 Mar 2021 10:04:43 GMT

Thanks Mayil.
Thought I needed a loop, but was going around this the wrong way....

Re: Creation of Table Using a Date Field in a Table

MayilVahanan — Thu, 11 Mar 2021 10:08:44 GMT

Welcome. Happy to help you.

And Appreciated, you thought correctly. Hope in future, will do it yourself.

Re: Creation of Table Using a Date Field in a Table

hermanitor — Mon, 15 Mar 2021 16:23:44 GMT

Just fyi.....
Was using the above table with my qvd load and found that it really did not like dates like 01/11/15....(DD-MM-YY)....
Had to load the table beforehand and take a day of where the dd was 01....
Quite an interesting issue to be honest

Steve